Monitor Database Growth

It is important to monitor your database growth, both for system maintenance, but also to plan for long term system resource requirements and potential performance issues.

There is no out-of-the-box way to show the growth information in SQL so you need to store some information on a periodic basis to be able to perform the analysis. This can be achieved with the following scripts:

Script 1: Create a database and set-up the table

Run the following script. This will create a new database called ‘SysMonitor’ and then create a table to store the information

USE [master];
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = ‘SysMonitor’) CREATE DATABASE [SysMonitor];
GO

USE [SysMonitor];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; SET ANSI_PADDING ON;
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = ‘TableSizeHistory’)
BEGIN;
CREATE TABLE [dbo].[TableSizeHistory](
[RowId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PK_TableSizeHistory PRIMARY KEY NONCLUSTERED,
[l1] [char](2) NULL,
[InsertTime] [datetime] NULL CONSTRAINT [DF_TableSizeHistory_InsertTime] DEFAULT (getdate()),
[Database] [nvarchar](128) NULL,
[Schema] [sysname] NOT NULL,
[Table] [sysname] NOT NULL,
[row_count] [int] NULL,
[reserved_MB] [int] NULL,
[data_MB] [int] NULL,
[index_size_MB] [int] NULL,
[unused_MB] [int] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [IXC_TableSizeHistory_InsertTime] ON [dbo].[TableSizeHistory] ([InsertTime]);

CREATE NONCLUSTERED INDEX [IX_TableSizeHistory_row_count_data_MB] ON [dbo].[TableSizeHistory] ([row_count], [data_MB])
INCLUDE ( [InsertTime],[Database],[Table],[reserved_MB],[index_size_MB]);
END;

Step 2: Create stored procedures

There are four stored procedures that need creating to be able to gather and process all the information.

USE [Master];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TableSize]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[sp_TableSize];
GO

CREATE PROCEDURE sp_TableSize
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
(ROW_NUMBER() OVER(ORDER BY t3.name, t2.name))%2 AS l1
,DB_NAME() AS [database]
,t3.name AS [schema]
,t2.name AS [table]
,t1.rows AS row_count
,((t1.reserved + ISNULL(a4.reserved,0))* 8) / 1024 AS reserved_MB
,(t1.data * 8) / 1024 AS data_MB
,((CASE WHEN (t1.used + ISNULL(a4.used,0)) > t1.data THEN (t1.used + ISNULL(a4.used,0)) – t1.data ELSE 0 END) * 8) /1024 AS index_size_MB
,((CASE WHEN (t1.reserved + ISNULL(a4.reserved,0)) > t1.used THEN (t1.reserved + ISNULL(a4.reserved,0)) – t1.used ELSE 0 END) * 8)/1024 AS unused_MB
INTO dbo.#Data
FROM
(SELECT
ps.object_id
,SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]
,SUM (ps.reserved_page_count) AS reserved
,SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data
,SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS t1
LEFT OUTER JOIN
(SELECT
it.parent_id
,SUM(ps.reserved_page_count) AS reserved
,SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = t1.object_id)
INNER JOIN sys.tables t2 ON ( t1.object_id = t2.object_id)
INNER JOIN sys.schemas t3 ON (t2.schema_id = t3.schema_id)
WHERE t2.type <> ‘S’ and t2.type <> ‘IT’;

Insert into SysMonitor.dbo.TableSizeHistory ([l1]
,[InsertTime]
,[Database]
,[Schema]
,[Table]
,[row_count]
,[reserved_MB]
,[data_MB]
,[index_size_MB]
,[unused_MB])
SELECT
l1
,CURRENT_TIMESTAMP
,DB_NAME() AS [database]
,[schema]
,[table]
,row_count
,reserved_MB
,data_MB
,index_size_MB
,unused_MB
FROM dbo.#Data ORDER BY reserved_MB DESC;
GO

USE [MASTER]; EXEC sp_ms_marksystemobject ‘sp_TableSize’;
GO

 

USE [SysMonitor];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExecuteDatabasesCommand]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[ExecuteDatabasesCommand];
GO

CREATE PROCEDURE ExecuteDatabasesCommand
(
@cmd nvarchar(4000)
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Data TABLE ([name] sysname);
INSERT @Data ([name])
SELECT [name] FROM sys.databases
WHERE [state] = 0 /* online */
AND database_id > 4 /* exclude system databases */

DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT [name] FROM @Data ORDER BY [name];

DECLARE @Database sysname, @Command nvarchar(4000);

OPEN cur;
SET NOCOUNT ON;

FETCH NEXT FROM cur INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @Command = ‘EXEC [‘ + @Database + ‘].[dbo].’ + @cmd;
EXEC (@Command);
FETCH NEXT FROM cur INTO @Database;
END;
CLOSE cur; DEALLOCATE cur;
GO

 

USE SysMonitor;
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataGrowth]’) AND type IN (N’P’, N’PC’))
DROP PROCEDURE [dbo].[DataGrowth];
GO

CREATE PROCEDURE DataGrowth
(
@StartDate datetime = NULL
,@EndDate datetime = NULL
,@Delta_MB int = 100
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

— Set the start date to 30 days back if no value provided
IF @StartDate IS NULL SELECT @StartDate = DATEADD(DAY, -30, CURRENT_TIMESTAMP);

— Set the EndDate to the current time if not value provided
IF @EndDate IS NULL SELECT @EndDate = CURRENT_TIMESTAMP;
— Get all data for the requested time period
SELECT
RowId
,[Database]
,[Schema]
,[Table]
,[row_count]
,[reserved_MB]
,[data_MB]
,[index_size_MB]
,[unused_MB]
INTO #Data
FROM dbo.TableSizeHistory
WHERE InsertTime BETWEEN @StartDate AND @EndDate
AND [row_count] <> 0
AND [data_MB] <> 0
GROUP BY [RowId], [Database], [Table], [Schema], [row_count], [reserved_MB], [data_MB], [index_size_MB], [unused_MB];

— get the max Id of each table
SELECT
[RowId]
,[Database]
,[Schema]
,[Table]
INTO #T1
FROM #Data
WHERE RowId IN (SELECT MAX(RowId) FROM #Data AS D
WHERE D.[Database] = #Data.[Database] AND D.[Schema] = #Data.[Schema] AND D.[Table] = #Data.[Table])
GROUP BY [RowId], [Database], [Schema], [Table];
— get the min Id of each table
SELECT
[RowId]
,[Database]
,[Schema]
,[Table]
INTO #T2
FROM #Data
WHERE RowId IN (SELECT MIN(RowId) FROM #Data AS D
WHERE D.[Database] = #Data.[Database] AND D.[Schema] = #Data.[Schema] AND D.[Table] = #Data.[Table])
GROUP BY [RowId], [Database], [Schema], [Table];
— Get the most recent data per each table
SELECT
[Database]
,[Schema]
,[Table]
,[row_count]
,reserved_MB
,data_MB
,index_size_MB
,unused_MB
INTO #Max
FROM #Data
WHERE RowId IN (SELECT RowId FROM #T1);
— Get the oldest data per each table
SELECT
[Database]
,[Schema]
,[Table]
,[row_count]
,reserved_MB
,data_MB
,index_size_MB
,unused_MB
INTO #Min
FROM #Data
WHERE RowId IN (SELECT RowId FROM #T2);

— Final output, get the delta and return to the client
SELECT DISTINCT
#Data.[Database]
,#Data.[Schema]
,#Data.[Table]
,#Max.row_count – #Min.row_count AS Delta_row_count
,#Max.reserved_MB – #Min.reserved_MB AS Delta_reserved_MB
,#Max.data_MB – #Min.data_MB AS Delta_data_MB
,#Max.index_size_MB – #Min.index_size_MB AS Delta_index_size_MB
,#Max.unused_MB – #Min.unused_MB AS Delta_unused_MB
FROM #Data
INNER JOIN #Max ON #Max.[Database] = #Data.[Database] AND #Max.[Schema] = #Data.[Schema] AND #Max.[Table] = #Data.[Table]
INNER JOIN #Min ON #Min.[Database] = #Data.[Database] AND #Min.[Schema] = #Data.[Schema] AND #Min.[Table] = #Data.[Table]
WHERE #Max.reserved_MB – #Min.reserved_MB > @Delta_MB
ORDER BY Delta_reserved_MB DESC, #Data.[Database], #Data.[Table], #Data.[Schema];
GO

 

USE [SysMonitor];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataGrowthDB]’) AND type IN (N’P’, N’PC’))
DROP PROCEDURE [dbo].[DataGrowthDB];
GO

CREATE PROCEDURE DataGrowthDB
(
@StartDate datetime = NULL
,@EndDate datetime = NULL
,@Delta_MB int = 100
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

— Set the start date to 30 days back if no value provided
IF @StartDate IS NULL SELECT @StartDate = DATEADD(DAY, -30, CURRENT_TIMESTAMP);

— Set the EndDate to the current time if not value provided
IF @EndDate IS NULL SELECT @EndDate = CURRENT_TIMESTAMP;
— Get all data for the requested time period
SELECT
[InsertTime]
,[Database]
,[Schema]
,sum([reserved_MB]) as Space_MB
INTO #Data
FROM dbo.TableSizeHistory
WHERE InsertTime BETWEEN @StartDate AND @EndDate
AND [reserved_MB] <> 0
GROUP BY [InsertTime], [Database], [Schema];

— get the max of each database
SELECT
[Database]
,[Schema]
,[Space_MB]
INTO #Max
FROM #Data
WHERE [InsertTime] IN (SELECT MAX([InsertTime]) FROM #Data AS D
WHERE D.[Database] = #Data.[Database] AND D.[Schema] = #Data.[Schema])
GROUP BY [Database], [Schema], [Space_MB];
— get the min of each table
SELECT
[Database]
,[Schema]
,[Space_MB]
INTO #Min
FROM #Data
WHERE [InsertTime] IN (SELECT MIN([InsertTime]) FROM #Data AS D
WHERE D.[Database] = #Data.[Database] AND D.[Schema] = #Data.[Schema])
GROUP BY [Database], [Schema], [Space_MB];

— Final output, get the delta and return to the client
SELECT DISTINCT
#Data.[Database]
,#Data.[Schema]
,#Max.[Space_MB] – #Min.[Space_MB] AS Delta_space_MB
FROM #Data
INNER JOIN #Max ON #Max.[Database] = #Data.[Database] AND #Max.[Schema] = #Data.[Schema]
INNER JOIN #Min ON #Min.[Database] = #Data.[Database] AND #Min.[Schema] = #Data.[Schema]
WHERE #Max.[Space_MB] – #Min.[Space_MB] > @Delta_MB
ORDER BY Delta_space_MB DESC, #Data.[Database], #Data.[Schema];

GO

Step 3: Build data

Create a new job in the SQL agent that should be scheduled to run daily or weekly, depending on what level of detail you want to store. This job should execute the stored procedure ExecuteDatabasesCommand  with the following command EXEC Sysmonitor.dbo.ExecuteDatabasesCommand @cmd = N'[sp_TableSize];’;

This is populate the table with all databases and tables on the SQL instance and store the row count and space used by each table.

Step 4: Analyse data

Periodically the data gathered needs to be reviewed and compared with growth expectations.

To analysis the data you can run one of two different stored procedures which will generate the delta change that has occurred for a specified period.

DataGrowth

Data growth will look at all changes that have occurred between a specified period (if not specified it will set to last 30 days) and display results where the delta change is greater than specified (default is 100 MB).

EXEC DataGrowth @StartDate = ‘2014-01-01’, @EndDate = ‘2014-06-01’, @Delta_MB = 100

EXEC DataGrowthDB

DataGrowthDB looks at the growth of the entire database between a specified period (if not specified it will set to last 30 days) and display results where the delta change is greater than specified (default is 100 MB).

EXEC DataGrowth @StartDate = ‘2014-01-01’, @EndDate = ‘2014-06-01’, @Delta_MB = 100

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s