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.