Tara Kizer Blog

Tara Kizer

How to track database growth across multiple SQL Server instances

It is easy to track database growth on a single SQL Server instance.  We simply just need to store the results of sp_databases or loop through the databases and call sp_spaceused for each database.

If you support hundreds of SQL instances like I do, you'd want to store the database growth information in a central repository.  From this central server, you could create a linked server for each SQL Server instance to track, but I hate creating linked servers.  I especially hate having to create hundreds of them on one SQL Server instance.  Instead of using linked servers, I created a CLR stored procedure.  It requires one table.

You can download the code here.  It includes the C# source code as well as the dll for the CLR object and a sample SQL script file to get it setup on your central server.

Once you have set it up, you can create a SQL job to call it.  If you have a small number of SQL instances to administer, you can simply add multiple calls to isp_DatabaseGrowth, like this:

EXEC dbo.isp_DatabaseGrowth 'Server1\Instance1'

EXEC dbo.isp_DatabaseGrowth 'Server2'

If you have a large number of SQL instances to administer, I recommend looping through a table that contains one row for every SQL instance.  Here is what my job step looks like:

DECLARE @serverId int, @serverName sysname, @max int 

SET @serverId = 1

SELECT IDENTITY(int, 1, 1) AS ServerId, ServerName INTO #Server FROM dbo.Server WHERE ServerName NOT IN ('Server1\Instance2', 'Server1\Instance3', 'Server3') –exclude certain SQL instances

SELECT @max = MAX(ServerId) FROM #Server

WHILE @serverId <= @max BEGIN SELECT @serverId = ServerId, @serverName = ServerName FROM #Server WHERE ServerId = @serverId

<span class="kwrd">EXEC</span> dbo.isp_DatabaseGrowth @serverName 

<span class="kwrd">SET</span> @serverId = @serverId + 1

END

DROP TABLE #Server

Here's the DDL for the Server table:
CREATE TABLE [dbo].[Server]
(
    [ServerName] [sysname] NOT NULL,
    CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED 
    (
        [ServerName] ASC
    )
)

If any of your databases were upgraded to SQL Server 2005, the data returned from sp_spaceused/sp_databases may contain incorrect data due to row count inaccuracies in the catalog views.  Make sure to run DBCC UPDATEUSAGE on your databases after an upgrade to SQL Server 2005.  Databases that were created in SQL Server 2005 do not have this issue.

Legacy Comments


Jerry Hung
2008-12-19
re: How to track database growth across multiple SQL Server instances
Do we need to deploy the CLR on every server to be monitored? Looks like it as it also creates a Table in it
It will be nice to collect all info and store them inside the centralized server instead of each one

that's what we do now... too bad we still need to deploy some SP's in the monitored servers
but on the Central Server, we schedule a job that calls remote SP's via OPENROWSET, and store the results in the Central Server

I wish we have MOM/SCOM in operation..

Tara
2008-12-19
re: How to track database growth across multiple SQL Server instances
You do not need to deploy the CLR object or the table to any other server, just the central server needs them. The central server also needs a SQL job.

So nothing is required on the other servers. That's the whole point of using an object like this.

DD
2008-12-26
re: How to track database growth across multiple SQL Server instances
GOOD STUFF

deepak
2009-02-26
re: How to track database growth across multiple SQL Server instances
Hi Tara,

How to set this procedure on SQL server ? Would like to know steps.

Regards,
Dee

deepak
2009-02-26
re: How to track database growth across multiple SQL Server instances
And one more silly one :) ..will it only work with 2005 + versions

Tara
2009-02-26
re: How to track database growth across multiple SQL Server instances
Deepak,

The CLR object must be created on a SQL Server 2005 instance. You should be able to pass a SQL Server 2000 instance name to the CLR object though as it is just running sp_databases. The output of that stored procedure is the same in both 2000 and 2005, so it should work fine.

I don't have any 2000 systems to test with though, so please let me know if it works or not.

~Tara