• subscribe
Search
Browse By: Author | Issue
 

Kimberly & Paul: SQL Server Questions Answered



Email Kimberly and Paul

Paul and Kimberly are a husband-and-wife team who own and run SQLskills.com, a world-renowned SQL Server consulting and training company. They’re both SQL Server MVPs and Microsoft Regional Directors, with more than 30 years of combined SQL Server experience.

Vendor-mandated regular database shrinking


Posted @ 12/1/2011 10:26 AM By Paul Randal

 

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!

Related Content:

Comments

Add A Comment
  • Posted @ December 12, 2011 05:11 PM by Jose Hernandez

    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?

  • Posted @ December 09, 2011 04:44 PM by Marc Jellinek

    ---
    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.

  • Posted @ December 09, 2011 04:24 PM by spacheco

    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

  • Posted @ December 09, 2011 03:30 PM by SQLTIME

    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.

  • Posted @ December 09, 2011 02:06 PM by Marc Jellinek

    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?

You must log on before posting a comment.

Are you a new visitor? Register Here


More about Kimberly and Paul . . .

Paul worked on the SQL Server team for nine years in development and management roles, writing many of the DBCC commands, and he was ultimately responsible for SQL Server 2008’s core storage engine. Paul blogs at SQLskills.com/blogs/Paul.

Kimberly worked on the SQL Server team in the early 1990s as a tester and writer before leaving to found SQLskills and embrace her passion for teaching and consulting. Kimberly blogs at SQLskills.com/blogs/Kimberly.

They've written Microsoft white papers and books for SQL Server 2008, 2005 and 2000, and they're regular, top-rated presenters worldwide on database maintenance, high availability, disaster recovery, design, performance tuning, and SQL Server internals. Together they teach SQLskills Immersion Events, which are wildly popular classes that teach the essential skills required to be an effective DBA; these classes also cover the required topics of the Microsoft Certified Master (MCM) certification.


© 2012 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.