Monday, May 26, 2008

Fragmentation with Shrink

Whenever I hear questions on database shrinking, always my first question would be “why you need to shrink”. It is not easy for me to understand why people always look for shrinking a database whenever they see some free space within the database. I always say, forget that the space there; it can be of use later. I would recommend database shrinking only in the following conditions –
1. The database is getting archived
2. We are damn sure that the database won’t grow anymore.
3. The database has a huge amount of free space and we need to restore the database to a server that doesn’t have enough disk space.
A database shrink has too many disadvantages –
1. Increased database fragmentation.
2. Increased file fragmentation.
3. The resources utilization to do the shrink and then the required growth in future
4. The user downtime during the growth and shrink process.
There is a small but great post from Paul Randal on the autoshrink and autogrowth processes, and how it increases fragmentation. Paul being one of the best SQL Server professionals in the world and a long time program manager in the Storage Engine team, I dont think there are many who can say something as convincingly as Paul on this topic. A must read post for any production DBA. See his post here http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

No comments: