Shrinking Data Files
We’ve all used DBCC SHRINKFILE or DBCC SHRINKDATABASE, right? It’s a really usefull tool. However, I stumbled across a blog by Paul Randall today and learnt something new. The shrink process can fragment your indexes!
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
Typically, DBA’s may shrink the database or data file following an index rebuild to reclaim the space. However, testing out Paul’s sample code, I discovered I had 99% fragmentation! This shocked me. Although I do not perform the shrink as part of the process, I would occasionally shrink some data files if our disks started alerting at 90%. In some cases, I’d reclaim back 25% of the database so it was a nice fix!
While Paul’s link demonstrates this issue by deleting a filler table, the same problem does occur if you simply add ramdom data to an index, rebuild the index and then shrink the data file – I tested this by inserting GUID values in batches.
This fragmentation only concerns the shrinking of the data files. Shrinking a log file does not cause the same problem.
So for all of you DBA’s out there who do use the shrink commands offered, think again, you could well undo all of your maintenance you have just performed!