It’s time for T-SQL Tuesday, this month hosted by Michael J Swart, our artistic DBA in the community. I love Michael’s pictures, and enjoy the time I get to chat with him at events.
His invitation this month for #52 is to argue against a popular opinion. He asking us to find a "never ever" issue and argue for it. Or perhaps an "always do this" advice and argue against that. It’s a great topic.
T-SQL Tuesday is the monthly blog party, invented by Adam Machanic. The second Tuesday of the month is the time to publish a post on your own blog about the topic and join the other people that write about the same topic.
Never Turn on Auto Shrink
I think that if you turn on the auto-shrink setting, Paul Randal rips an LED off his interactive panels. Even if he doesn’t, why would you ever enable auto shrink? It causes fragmentation, it’s somewhat insane as your files will just regrow if the space is needed for a workload such as a maintenance operation.
The recommendation is to never turn on Auto Shrink on your databases. There are numerous references, though I’ll point to Brent Ozar’s nice summary. I’m going to pick a fight with this advice, though only in a narrow, limited sense.
Auto shrink is, in general, a very bad idea. However I think there are a few places where I’d use the setting, understanding the implications.
The Road Warrior
There was a salesperson that had a laptop, traveling around and working his a piece of SQL Server (Express) based software. Let’s call him Joe, since that’s a nice generic name for this story.
When Joe arrived at a client site, he’d get data from the clients in an Excel spreadsheet. He’d use the import wizard, load a bunch of data into a new table, point his software at it, and do some analysis of the data. He’d then make recommendations to the client and sell something based on his recommendations. When he was done, he’d click a button in the software and drop the table. The amount of data from clients varied, and it sometimes caused his database (and log files) to grow. He’d also forget to the drop the table sometimes and would wind up with 4-5 clients’ worth of data before he removed the tables.
Over time, this also caused his laptop to run low on space. This wasn’t a big deal, and the help desk could respond to his concerns by remotely running a shrink of the files for him. However the time spent dealing with questions, and the concern that Joe experienced weren’t worth the effort. Auto-shrink worked fine for Joe and some of his colleagues.
Performance wasn’t a big concern, as a single user accessed the database. The data didn’t live long enough to worry about fragmentation or maintenance and the reduction in irritation from Joe and the help desk was worth it.
The Dusty Archives
I once worked in a company that had an archive database. We had a process that copied data from previous years to a new database that was taken offline and a copy burned to a few optical discs for long term archival. We actually build this process to use a separate instance for the copy as the source SQL Servers were located in various parts of the US, and the data was remotely transferred to a central location for archival, and because we didn’t want to ship optical drives to multiple locations and explain to warehouse personnel how to use them.
I, and my cohorts in IT, also didn’t really want to travel to small, inconvenient warehouses located in small cities without direct flights.
One of the requirements was that we make these databases as small as possible to save space. We typically built a few archive databases before we shrank them and took them offline. In hindsight, the process of copying the data should have minimized the size of data and log files with its own shrink, but that wasn’t the case. An administrator needed to perform the shrink and sometimes they’d forget. Actually, most of the time they’d forget.
The simplest solution was to enable auto shrink on this instance. That way once database files were sitting on the instance for any length of time, the databases would shrink themselves. No need for anyone to worry.
Performance wasn’t an issue in these cases as recovery of the data would have meant a transfer back to a source system, where fragmentation and indexing would have been a concern.
Narrow Cases
In both cases, the reason auto shrink worked was performance wasn’t an issue, and administration was. Performance didn’t matter, and it wasn’t a heavily used system. I think these, and perhaps a few other narrowly defined cases are acceptable uses of Auto Shrink.
However I also think there are many more abuses of the setting and I’d prefer it removed from SQL Server. Scheduling this process with Powershell would be trivial if you really needed it.
And if you don’t know how to use Powershell (or SQLCMD), perhaps you shouldn’t be shrinking database files.
