Shrinking Database Files – Let’s Not And Say We Did

— by Lori Brown  @SQLSupahStah

I recently ran into someone who swears that shrinking database files on a production server is a good thing and proudly said they did it on all systems they work on.  OMG!!  Let me post a quote directly from MSDN regarding weather or not shrinking database files causes fragmentation (https://msdn.microsoft.com/en-us/library/ms189493.aspx) :

Please notice I am talking about production SQL Servers and not development, test, QA, staging or anything that is not production.  Of course if you shrink database files in any environment you are still going to cause more fragmentation but since the use of those systems is often vastly different than a production system, you as the DBA have to judge the ramifications on those non-production systems.

Have I ever had to shrink database files on the production systems I monitor…Uhhh, yes.  But, I do so very infrequently and with reason.  I even have one system where the guy who is in charge of it refuses to allow me to disable the nightly job that shrinks some of the databases.  I can only do so much but he can’t say I have not duly warned him.

There are tons of good articles and even rants of why you should not shrink your database files.  Here are some links in case you think I am the only one telling you to NOT shrink your database files.

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/  – Gotta love some Ozar!

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ – From THE man!

http://www.sqlservercentral.com/blogs/simple-sql-server/2016/01/19/shrinking-database-data-files/ – Good post from a new blogger with the appropriate warnings.

https://www.am2.co/2016/04/shrink-database-4-easy-steps/ – Lots of warnings throughout the post.

https://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/ – Shows a tool from sysinternals that allows you to see the physical fragmentation and states that once db files are physically fragmented the only way to undo the damage is to take your databases offline and defrag them at the disk level.

If you go to the links, you may be thinking that some of those posts are old.  However, look through the comments…there are new ones up to the present and they are still getting the same answers. 

If after all that you are still not convinced that shrinking database files causes physical fragmentation, well… I tried.  But go ahead and keep at it.  If you are lucky you may not ever have an issue or experience things getting slow because of fragmentation.  Roll those dice!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!