Post reply

Database Shrink

  • August 13, 2010 at 1:27 am

    #224659

    Hi All,

    I have a question with database shrink and Index fill factor.

    if i am using fill factor as 70.

    means 30% pages are free.

    now if i do shrinking after any insertion and deletion commands, does it affect my fill factor too ???

    or pages will remain 30% free after shrinking task

  • August 13, 2010 at 4:31 am

    #1206492

    Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Shrinking only removes empty pages. It doesn't remove space on the pages, so pages that are x% full before a shrink will still be x% full after a shrink.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2010 at 4:58 am

    #1206504

    The shrink routine is pretty simple - essentially, it just moves whole pages from the end of the file toward the beginning, to free up space at the end of the file so it can be shrunk. Since it doesn't do much to the pages themselves, it won't affect the fullness of pages.

    Remember that the fill factor is only enforced at the time an index is built - it is not maintained by the system as insert, update, and delete operations take place.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 13, 2010 at 6:15 am

    #1206549

    Paul White NZ (8/13/2010)

    The shrink routine is pretty simple - essentially, it just moves whole pages from the end of the file toward the beginning, to free up space at the end of the file so it can be shrunk. Since it doesn't do much to the pages themselves, it won't affect the fullness of pages.

    Remember that the fill factor is only enforced at the time an index is built - it is not maintained by the system as insert, update, and delete operations take place.

    This is correct but will send your fragmentation through the roof :w00t: If you rebuild your indexes to remove this, the fraged data will be nicely ordered again but at the end of the file where it was originally with a big hole in the middle again ! :hehe:

    Rule of thumb is not to shrink database files - think about getting more disk space if you are running short.

    Hope this helps ...

  • August 13, 2010 at 6:27 am

    #1206562

    Mark_Pratt (8/13/2010)

    This is correct but will send your fragmentation through the roof :w00t: If you rebuild your indexes to remove this, the fraged data will be nicely ordered again but at the end of the file where it was originally with a big hole in the middle again!

    If the database is shrunk before the index rebuild (leaving sufficient space for the rebuild, and/or rebuild sorts done in tempdb) it'll be fine.

    Rule of thumb is not to shrink database files - think about getting more disk space if you are running short.

    Yep. But it is ok to shrink a database if you have a good reason. There, I said it.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • August 13, 2010 at 9:51 am

    #1206764

    @ Gail :

    thanks for your answer, i just want to know does shrink affect free space on pages, it is clear now Thanks

    as you asked why you want to shrink database.

    so, we have some archiving jobs schedule to archive data from live to offline database.

    dont you think we should archive our database after ARCHIVING activity ??

  • August 13, 2010 at 10:29 am

    #1206785

    fawwad (8/13/2010)

    dont you think we should archive our database after ARCHIVING activity ??

    Depends. How much are you archiving? How much space is free after? How long will it take new data to use that space up? How often do you archive?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 13, 2010 at 11:10 am

    #1206803

    If you're running Enterprise Edition, consider setting up an archiving scheme based on partitioning. It's much easier, faster, and better!

    http://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply