Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

SQLDIY: Database Space Detail

Continuing to build up our data gathering library we shift our focus to tracking database file growth. This particular procedure compiles both data and log file usage. This is a fundamental aspect of tracking growth over time and being proactive about growing your databases yourself instead of letting the auto grow making  a mess of your databases. There is no clean and easy way to do this without temp tables so I just have to suck it up and use them. I considered going with a CLR routine and gather more data but we are assuming that you, the user, don’t have the CLR or OLE automation enabled. Personally, I wouldn’t have OLE automation enabled if at all possible. It is another surface area to secure and another source of odd memory issues. There are similar issues with the CLR. Other than the security concerns when there is memory pressure the CLR is ejected and may not be able to load again until the SQL Server process is recycled. With all that in mind I present to you Database Space Detail schema and script!

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Database Space Detail

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!


Comments

Posted by Jason Brimhall on 28 June 2011

good stuff - keep it coming.

Leave a Comment

Please register or log in to leave a comment.