Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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 

    EXEC dbo.isp_DatabaseGrowth @serverName 

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

Print | posted on Tuesday, December 16, 2008 10:28 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# 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..
12/19/2008 10:24 AM | Jerry Hung
Gravatar

# 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.
12/19/2008 10:32 AM | Tara
Gravatar

# re: How to track database growth across multiple SQL Server instances

GOOD STUFF
12/26/2008 9:22 PM | DD
Gravatar

# 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
2/26/2009 2:50 AM | deepak
Gravatar

# re: How to track database growth across multiple SQL Server instances

And one more silly one :) ..will it only work with 2005 + versions
2/26/2009 3:17 AM | deepak
Gravatar

# 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
2/26/2009 10:15 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET