Vendor-mandated regular database shrinking

Question: I’m having problems with an application vendor who is mandating that we run regular DBCC SHRINKDATABASE operations against the application databases and tempdb. They say this is necessary for performance – can you give me some advice please?

Answer: I get asked this at least once a month, where an application vendor refuses to allow a DBA to remove regular shrink operations because they’re ‘necessary for performance’.

We all know that shrinking databases causes index fragmentation, consumes CPU and IO resources, and generates a lot of transaction log (which can cause problems for mirroring, replication, and so on). We also know there are some extenuating circumstances where occasional one-off shrinks may be necessary.

Furthermore, we know that *regularly* shrinking databases is a cardinal sin because if the database repeatedly grows after being shrunk, all that shrink work is completely wasted effort – its akin to having auto-shrink enabled for the database.

The problem I see is that the vendor application teams *don’t* know these things about shrink, and are loath to listen to anyone trying to educate them.

Occasionally I’ll jump on an email thread with the original sender and the application vendor team. The justifications from the application vendor team are usually along the lines of the following (paraphrasing):

  • The indexes in the database are already fragmented so shrinking doesn’t make it any worse.
  • Nobody’s ever complained about performance before so why are you?
  • We have to have a regular shrink because the operations we do cause the database to expand a lot and customers want their disk space back.
  • We have to shrink tempdb because the operations we do cause it to grow continually.

None of these are valid reasons for regularly shrinking databases, and in fact it’s documented in KB article 307487 that shrinking tempdb when there’s user activity can lead to tempdb corruption, and the Working with Tempdb in SQL Server 2005 whitepaper (applicable to all versions) states that “Shrinking files is not a recommended practice…”

Any time a vendor states that shrinking is necessary, it either demonstrates a fundamental misunderstanding of how SQL Server should be managed or a deficiency in the application’s behavior that is being covered up through regular shrinking – in my opinion.

Feel free to pull me into an email conversation with vendors who argue that shrink is necessary!

Discuss this Blog Entry 12

on Dec 1, 2011
One of my [least] favorite arguments is, "I read on a blog post that it's not that bad." This was actually in regards to using clustered nonsequential GUIDs on all tables, but I still think it's relevant.
on Dec 2, 2011
This advice makes sense if the database/application is transactional but what happens in the case where the vendor's application does its work, spits out the results and those results dont change. They re essentially static; used for reporting only. The next time the application runs, it spits the results out to a new database. Would you recommend using DBCC SHRINKDATABASE (for both log and database files) to reclaim disk space in this scenario? (This is the situation Im in now. The analysis software used by our team creates huge results databases, sometimes hundreds of gigabytes in size. The vendor recommends shrinking periodically to regain storage space both from logs and the database. Recovery mode is set to SIMPLE for all results db. TempDB is set to a static size and never changed unless deemed to be too small. Autoshrink is frowned upon as it might tie up server resources at critical times such as when another analysis is running. Periodic targeted shrinking allows us to control storage use).
on Dec 1, 2011
If you need a rescuer, Paul will have your back astonishingly. He helped me deal with some hard-headed and stubborn vendors. Many Thanks Paul!
on Dec 1, 2011
Super Article...and Many thanks Paul. I will surely assure to add you this time in my nasty :) mail converstaions about illogical explanations of sql server. Thanks, Aditya
on Dec 9, 2011
Here's my reply to the vendor arguments: "Indexes are fragmented already, so it doesn't make a difference" - regular database maintenance should include index defragmentation. "No one has ever complained about performance" - refer them to the thousands of public comments on a variety of websites (if a major application like BackupExec or Blackberry Enterprise Server) "Customers want their disk space back" - but then you are only going to consume it again during the next processing cycle. Shrinking the data files give the customer the impression that they have more disk space available than they actually have. If you shrink the data files, then use the "reclaimed" disk space for something else, then the application causes the data files to grow (and consume all available disk space)... SQL Server falls over (if we're talking about tempdb) or the database is unable to process additional transactions. This advice makes the database less performant and less reliable. As a professional, this is not advice I can follow. Better to accurately tell your customers how much disk space is going to be required so they can plan and budget to have it available. "We have to shrink tempdb because the operations we do cause it to grow continually" It sounds like you are using cursors and temp tables, then not releasing them. Space within tempdb will be reclaimed and reused (just like any other SIMPLE recovery model database) once the transactions that pertain to the data are committed. Shrinking the database files will NOT recover space from transactions that are still "in-flight", so this advice doesn't make sense to me. It's advice like this that leads me to believe the majority of products that do this are written for companies that do not have a professional DBA on staff. If that's the case, I strongly encourage those companies to evaluated cloud-based alternatives for their applications. They will have a DBA on staff.
on Dec 2, 2011
I am currently working for a company on contract and the incumbent DBA has the same line - all databases need to be shrunk after backup. When I tried to explain why this should not happen (using articles written by Paul et al) I was told that nobody had every question it before and that is the way it is staying!
on Dec 1, 2011
I have my own horror story related to this. I was actually forced to find another job many moons ago because it, in fact. At the company I was with at the time, some management turnover occurred and a new system administrator came on-board who had a lot of IT friends. Over the course of 3-4 months, most of the existing IT/helpdesk staff was replaced with personal friends of his. Apparently my turn came up and he convinced the director to bring in a consultant (yes, one of his buddies) to review things. Shortly after his review, I was brought before him, the sysadmin, the director and the programming manager. I was interrogated about why databases were not being shrunk regularly or automatically. Despite every effort, they were not going to be convinced that this (highly paid) consultant didn't know what he was talking about. I gave notice, promptly got walked out (for security reasons) and landed the best job I've ever had two weeks later.
spacheco (not verified)
on Dec 9, 2011
I recently had to (very very relunctantly) shrink a production database. A developer had created a table to log error messages for his app without letting me know (going forward He'll write these errors to the windows app log.) This table caused the database to fill up all available hard disk space until SQL stopped allowing transactions. I truncated the table but still had this huge mdf. The shrink operation reduced the size of the DB from 20GB to 2 GB. Given more time, could I have backed up the database(s), perforn the shrink, taken the SQL service down and performed a disk defrag on the mdf and ldf arrays? Alternatively, could I have shrunk the db, backed it up, dropped the database, recreate the DB and restore over it with the backup? Are these viable solutions? Thanks, Serg
on Dec 12, 2011
Paul/Kimberly, I'm gonna have to take you on your offer of help. I'm currently going back and forth with two developers working as contractor at my workplace that are making a push for some regular maintenance involving shrinking log files on SQL databases. This is not the first time they've pushed for this, it seems to come up every time they see a large log file and in their own opinion, 'no log file should be over 1Gig ever'. I've tried my best to explain why this is a bad idea. I've sent them the link to this blog post and countless others and now the argument is boiling down to them saying, 'but they are talking about not shrinking the database file, not the log file'. I'm tired of the same argument but it doesn't seem my word is carrying any weight so if you could help me to better put this to rest I would appreciate it. Maybe I could send you the email thread and you could point if I'm not communicating this properly?
on Dec 9, 2011
One last comment: Not only does SHRINKDATAFILE or SHRINKDB cause index fragmentation, it causes file system fragmentation. I've never been able to get a handle on the real impact of file system fragmentation on performance with SQL Server, especially where data and transaction log files are stored on SANs or RAID arrays. One would assume that log files (which are read and written sequentially and thus should be stored on RAID1 or RAID 10 media) that having the read/write heads go in one smooth arc across the platters would be beneficial, but you can't assume that on a RAID or SAN environment. Any input on the impact of filesystem fragmentation of data and transaction log files?
on Dec 9, 2011
CRM 2011 has a shrinkdatabase that runs as a system scheduled job about every 3-4hours - should I contact the SQL team or the Dynamics CRM team on this? I want to gut out the stored procedures that runs it but I probably wont be supported if I do that. We upgraded in place from CRM4 so hoping THAT is reason this is going on. We dont see anything in the system jobs - looks to be setup that way by default CRM 2011 install.
on Dec 9, 2011
--- A developer had created a table to log error messages for his app without letting me know (going forward He'll write these errors to the windows app log.) --- Rather than writing the logs to tables or windows application logs, write them to a text file. Windows Application Event Log won't fill generally fill up, but it will generally reach a maximum size, then start removing older entries. It's likely that your developer's logs will wipe out any information in the Windows Application Event Log. See my blog at marcjellinek.wordpress.com for a SQLCLR function to write to a text file. --- Given more time, could I have backed up the database(s), perforn the shrink, taken the SQL service down and performed a disk defrag on the mdf and ldf arrays? --- That's probably your best bet. You should also rebuild or reorganize any fragmented indexes. --- Alternatively, could I have shrunk the db, backed it up, dropped the database, recreate the DB and restore over it with the backup? --- That might take care of the majority of your file fragmentation, but it would leave the indexes fragmented. Given the size of your database (2-20GB isn't all that large), you should be fine.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×