Database Shrink

Question

Database Shrink

fawwad

SSC Eights!

Points: 965
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

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

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 3

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 ...

Paul White SSC Guru Points: 150467 More actions · Answer 4

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

fawwad SSC Eights! Points: 965 More actions · Answer 5

@ 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 ??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

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