locked
DBCC Shrinkfile RRS feed

  • Question

  • Does DBCC Shrinkfile arrange the pages in reverse order 
    Tuesday, March 17, 2015 11:17 PM

Answers

  • Hi

    The short answer is no.

    When shrinking a data file it does the following

    • Scans the file backward, using GAM and PFS pages to find the highest page number allocated in the file
    • Obtains an exclusive lock in the page
    • Moves the records on the page to the lowest available empty page in the file, taking into account the allocation unit of which the page is part

    The above steps are repeated until the shrink operation has created the required amount of free space or a page that cannot be moved is encountered. (sourced from SQL Server 2012 Internals).

    Of course if the TRUNCATEONLY option is used it won't move any pages around, just freeing up space to the OS based on the location of the last allocated extent.

    A simpler process is used for log files where any inactive virtual log files at the end of the file are removed. 

    As you can imagine this creates a lot of IO, transaction log growth, and results in massive fragmentation so is generally a bad idea.

    Hope that helps

    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:22 AM
    • Unproposed as answer by Michelle Li Wednesday, March 18, 2015 11:22 AM
    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:23 AM
    • Marked as answer by Michelle Li Friday, March 27, 2015 12:41 PM
    Tuesday, March 17, 2015 11:38 PM
  • /* A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.*/

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:17 AM
    • Unproposed as answer by Michelle Li Wednesday, March 18, 2015 11:23 AM
    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:24 AM
    • Marked as answer by Michelle Li Friday, March 27, 2015 12:41 PM
    Wednesday, March 18, 2015 6:44 AM

All replies

  • Is this what you are referring to? It sounds like some files will be moved to the beginning of the file, but it does not appear to place all files in reverse order.

    NOTRUNCATE

    Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.

    https://msdn.microsoft.com/en-us/library/ms189493.aspx

    • Proposed as answer by Uri DimantMVP Wednesday, March 18, 2015 6:43 AM
    • Unproposed as answer by Uri DimantMVP Wednesday, March 18, 2015 6:43 AM
    Tuesday, March 17, 2015 11:26 PM
  • Hi

    The short answer is no.

    When shrinking a data file it does the following

    • Scans the file backward, using GAM and PFS pages to find the highest page number allocated in the file
    • Obtains an exclusive lock in the page
    • Moves the records on the page to the lowest available empty page in the file, taking into account the allocation unit of which the page is part

    The above steps are repeated until the shrink operation has created the required amount of free space or a page that cannot be moved is encountered. (sourced from SQL Server 2012 Internals).

    Of course if the TRUNCATEONLY option is used it won't move any pages around, just freeing up space to the OS based on the location of the last allocated extent.

    A simpler process is used for log files where any inactive virtual log files at the end of the file are removed. 

    As you can imagine this creates a lot of IO, transaction log growth, and results in massive fragmentation so is generally a bad idea.

    Hope that helps

    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:22 AM
    • Unproposed as answer by Michelle Li Wednesday, March 18, 2015 11:22 AM
    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:23 AM
    • Marked as answer by Michelle Li Friday, March 27, 2015 12:41 PM
    Tuesday, March 17, 2015 11:38 PM
  • /* A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented.*/

    http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:17 AM
    • Unproposed as answer by Michelle Li Wednesday, March 18, 2015 11:23 AM
    • Proposed as answer by Michelle Li Wednesday, March 18, 2015 11:24 AM
    • Marked as answer by Michelle Li Friday, March 27, 2015 12:41 PM
    Wednesday, March 18, 2015 6:44 AM