locked
Releasing unallocated space from database in best way. RRS feed

  • Question

  • Hello all please suggest the best possible way to release unallocated space from the Database. 

    Details: 

    database_name  database_size unallocated space
     XYZDB                 1251056.63 MB           407747.23 MB

    As you can see the DB size has increased. On deleting some of the tables i got a lot of unallocated space. 

    -- 

    Condition: 

    1. Partition on tables 

    2. Indexes on each partition 

    3. Performance should not degrade 

    ----

    Information FYI: 

    On each ETL run indexes are drooped and created 

    Please Help :)

     


    Monday, February 10, 2014 11:45 AM

Answers

  • Hi SQL Learner111,

    The DBCC SHRINKFILE command enables us to shrink one data file or log file at a time for a specific database. While the DBCC SHRINKDATABASE command enables us to shrink all data and log files at a time for a specific database. So, either one is okay.

    Regarding the NOTRUNCATE and TRUNCATEONLY options, the NOTRUNCATE option moves allocated pages from the end of a data file to unallocated pages in the front of the file and the fee space at the end of the file is not released, hence, the physical size of the file doesn’t change looks like the file is not shrinked.  The TRUNCATEONLY option release all free space at the end of the file to the operating system, and doesn’t perform page movement inside the file.

    When we use DBCCSHRINKDATABASE command, NOTRUNCATE only affects data files, and TRUNCATEONLY only affects log file. When we use DBCCSHRINKFILE command, both of NOTRUNCATE and TRUNCATEONLY affect data files not log files.

    For example, if using DBCC SHRINKDATABASE command, you can use DBCC SHRINKDATABASE (TestDB, 10) to shrink both data file and log files and allow for 10 percent free space in the database, or use DBCC SHRINKDATABASE (TestDB, TRUNCATEONLY) to shrink data and log files to the last allocated extent.

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Wednesday, February 19, 2014 3:57 PM
    Friday, February 14, 2014 1:06 PM

All replies

  • Why do you want to release unallocated space?  It causes problems when your database files grow and then you shrink them.

    There are a couple situations that I would consider shrinking files. One is where you have an completely abnormal growth in your file.  For example, you stage a very large table once in your database and then drop the table.  And you have no plans to load another large table for a long time.  A second is where you move a table (especially a large one) to a new file group and don't anticipate filling the original file for a very long time.

    You can use the DBCC SHRINKFILE command (http://msdn.microsoft.com/en-us/library/ms189493.aspx ).


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, February 10, 2014 7:32 PM
  • Hello, 

    which one do you suggest  

    DBCC SHRINKFILE NOTRUNCATE

    or 

    DBCC SHRINKFILE TRUNCATEONLY

    And explain why?? As NOTRUNCATE will cause fragmentation

    Also why not DBCC SHRINKDATABASE 

    Tuesday, February 11, 2014 5:13 AM
  • DBCC ShrinkDatabase will work as well.

    I have so rarely used these.  You might have better luck with these questions in a group that is devoted to SQL administration.

    By the way, my main point in responding earlier was not to give ways to shrink the database.  My point was to caution you against shrinking.  It is overused.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Edited by Russ Loski Tuesday, February 11, 2014 11:30 AM
    Tuesday, February 11, 2014 11:23 AM
  • Hi SQL Learner111,

    The DBCC SHRINKFILE command enables us to shrink one data file or log file at a time for a specific database. While the DBCC SHRINKDATABASE command enables us to shrink all data and log files at a time for a specific database. So, either one is okay.

    Regarding the NOTRUNCATE and TRUNCATEONLY options, the NOTRUNCATE option moves allocated pages from the end of a data file to unallocated pages in the front of the file and the fee space at the end of the file is not released, hence, the physical size of the file doesn’t change looks like the file is not shrinked.  The TRUNCATEONLY option release all free space at the end of the file to the operating system, and doesn’t perform page movement inside the file.

    When we use DBCCSHRINKDATABASE command, NOTRUNCATE only affects data files, and TRUNCATEONLY only affects log file. When we use DBCCSHRINKFILE command, both of NOTRUNCATE and TRUNCATEONLY affect data files not log files.

    For example, if using DBCC SHRINKDATABASE command, you can use DBCC SHRINKDATABASE (TestDB, 10) to shrink both data file and log files and allow for 10 percent free space in the database, or use DBCC SHRINKDATABASE (TestDB, TRUNCATEONLY) to shrink data and log files to the last allocated extent.

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Wednesday, February 19, 2014 3:57 PM
    Friday, February 14, 2014 1:06 PM