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!
