Introduction
While attending a pre-conference session at SQLPass, I was captivated reading a slide regarding 'database capacity planning'. Paul Randal and Kimberly Tripp state that you should not rely on the 'arbitrary' autogrow (or even worse autoshrink) features of SQL server, but instead you should plan ahead. Proper management should allow you to grow your databases (or logs) in a co-ordinated fashion. I wondered if the databases that I knew were properly dimensioned…
Is my database growing ?
But, how to know if autogrow/shrink occurs ? I asked a few other people at SQLPass, but no luck. When fiddling with management studio, I stumbled on the answer I was looking for:
When you right click on a database, you can request 'Disk Usage' data. In the resulting page, you will find a button at the bottom of the page called 'Data/Log files Autogrow/Autoshrink Events'. When you click that button, you will get a result like this one (this is actually the result for the master database, running SQL Denali CTP1):
BINGO ! Now it's time to fire up SQL Profiler and intercept the query that generates this data. Using this technique I found out that Management Studio is actually querying a trace file ?!
It turns out that since SQL 2005 Microsoft introduced a 'default trace'. It's this default trace that also captures any shrink or grow events. A little change in the captured T-SQL code gave me the code necessary to query a server for shrink or grow events for ALL databases.
Putting everything in place
Running this altered code, I used my favorite BI tool (aka Excel) to checkout a server, with these results:
I changed the names of the databases, but you can see what's happening to this production server:
- 'Database 1' is an internal developed/maintained database, that reached the limit of it's original database size. Once that size was reached, the database was set to grow with '1 Mb, unlimited growth'. You can see that this resulted in more the 400 database grows per month! To make things worse, this server didn't have instant initialization turned on.
- 'Database 2' is a commercial solution for monitoring customer credit. It turns out this database had the 'autoshrink' feature turned on. This resulted in 400 autogrows, compensated by 300 autoshrinks of the log file per month. Can you spell inefficiency ?
- 'Database 3' is a commercial fax solution that uses SQL server as a backend storage. This looks fine, although it indicates a few logfile grows per month. Upon further investigation, it turned out that the database was in 'FULL' recovery model, but that the IT admin never ran a log backup (because he doesn't know SQL and saw an option in his backup software called 'full backup'). We just avoided another disaster here, since the log file was set to a limited size. A few extra log file grows and the database was down. No faxes any more.
- 'tempdb' looks under dimensioned for the moment: time to reset it's initial size to avoid all those database and logfile grows !
Conclusion
I learned a lot wandering down this path. I even installed a similar 'default' trace on a SQL2000 box I manage, in order to capture just these grow/shrink events. If I did my planning homework correctly, I should never see any uncontrolled grow/shrink in the future. And of course, neither should you…