I Was In The Pool! Dealing With SQL Shrinkage.
Yes, shrinking your SQL Server databases is a bad thing. It is the best way to singlehandly fragment all of your indexes and heaps to 99%, thereby killing performance to a point just shy of what can be done by loading a quesadilla into the DVD drive or kicking the power cord out of the server.
I never recommend shrinking the data or log files of a SQL database.
That being said, I shrunk 319 SQL databases on one of my instances this past Monday due to space issues. I felt dirty, and no number of Silkwood showers made me feel clean. It was necessary though, and yes I said 319.
We have a piece of software that was undoubtedly written and distributed by three teams of 7 year-olds who did not consult with each other during the coding process. They were each locked in separate rooms and told to write a product that produced redacted to avoid being sued for telling the truth. They were fed fast food, were only allowed 20 minutes of sleep nightly, and were forced to listen to a podcast of Amy Winehouse reading nursery rhymes on an endless loop. They apparently were also plied with Hamm’s beer and spray cheese to make their minds soft. After 3 weeks of coding they were told they were done. Then their handlers took each of their finished products and meshed every third line from each team; they slapped it in a box and sent it out the door.
Apparently our Human Resources Department lost a bet and now we own the product.
This product redacted to avoid being sued for telling the truth and each time it redacted to avoid being sued for telling the truth it creates a new database on the SQL instance. The model database was configured to have a 25Mb data file and a 15Mb transaction log file. The sheer count of databases and their associated nightly backup gummed up the hard drive; forcing me to dirty my hands a bit. So, what did I do? I understand you’re asking for a friend, right?
Created a cursor (you heard me I said C to the muddafreakin’ USRSOR!) to dynamically build a script to shrink each .mdf to 5Mb and each .ldf to 2Mb for each database other than master, msdb, and tempdb. The script also set autogrow to 2Mb for each file. (These files were tiny, would never grow, and frankly were glorified Access databases files. (Funny, I almost said Access and database in the same sentence, har har!)
Ran the dynamic script.
Ran my nightly index rebuild script to fix what I had done.
Looked around, made sure no one saw me, and quietly shuffled away from the server.
Sure we tell you not to do stupid things to your SQL instances: never turn on auto shrink, never turn on auto close, never NOT back up your databases, never use sa for anything – ever. Those things we still stand by. However, there are times you may need to shrink a database or not backup a database: the important thing is to know when it’s appropriate to do inappropriate things in SQL Server and you also need to understand and deal with the results of your actions.