Because SQL Server is one of the most popular database platforms, the number of SQL Server instances tends to grow once it has a toehold in an enterprise. Many enterprises develop applications to run on SQL Server, and SQL Server often rides in on the coattails of other Microsoft products such as SharePoint and Systems Center Operations Manager, as well as third-party applications purchased for specific needs. This rapid growth in the enterprise can make it difficult to properly manage all the SQL Server instances in the enterprise in a centralized manner.
That’s where this month’s free tool can come in handy. The Database Growth Tracker is a centralized database growth tracking utility that was written by Tara Kizer, a SQL Server DBA and Microsoft MVP. (You can read Kizer’s blog at weblogs.sqlteam.com/tarad.) Kizer offers a variety of other interesting custom database maintenance routines at weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx.) The Database Growth Tracker lets you centrally query and track the amount of space used by databases on as many SQL Server instances as you like, without the messiness of linked servers. Let’s look at how the Database Growth Tracker works.
Tracking Space Consumption
This tool uses a CLR stored procedure called isp_DatabaseGrowth to query databases for space utilization. The CLR stored procedure queries the target SQL Server instance and database for space consumed using sp_spaceused and sp_databases. (Under certain circumstances, such as when a SQL Server 2000 database has recently been upgraded to SQL Server 2005, the results of sp_spaceused might be inaccurate. In those cases, you should make sure DBCC UPDATEUSAGE has been run recently to ensure accurate row counts.) The Database Growth Tracker uses a single table to track the SQL Server instances in which isp_DatabaseGrowth will run and retrieve space consumption information.
You can collect the information returned by isp_DatabaseGrowth daily in a table or a Microsoft Excel spreadsheet to look at trending and the server’s overall space consumption. Of course, this tool is limited to returning the information that’s typically returned by the system stored procedures that it calls, namely sp_spaceused and sp_databases.
System Requirements
You can download the Database Growth Tracker from Kizer’s blog at weblogs.sqlteam.com/tarad/archive/2008/12/16/How-to-track-database-growth-across-multiple-SQL-Server-instances.aspx. (The link to the downloadable code is in the first sentence of the third paragraph.) On the blog post, you’ll find both the SQL code that creates the server table and the T-SQL code that executes isp_DatabaseGrowth on each target SQL Server instance and database. In addition, Kizer provides both the C# source code for the CLR object and the compiled DLL, as well as a sample SQL script to help you get the compiled DLL configured on your central server. Kizer recommends that you regularly run isp_DatabaseGrowth from a SQL Server Agent job.
The Database Growth Tracker runs on SQL Server 2005 and later. The tool otherwise conforms to the requirements of a typical CLR object.
|
Database Growth Tracker |
