SQL DBA Guru

How to Monitor Database Growth trend and do capacity planning

One of the key tasks of a DBA is to keep an eye on the growth pattern of the databases. That includes both the data files and the log files including all the secondary files, if any. To capture a trend of the database size, we need to create a table and then record the database size on a regular basis.

Following script creates a table"DBA_DBGrowth" and saves all the databases' size for a single instance. For capturing the same for multiple servers and multiple instances, you need to execute the following script per instance per server.

As a standard, I have creates a dedicated database in each server for capturing the DBA stuff .

USE [DBA_DB]
GO

IF OBJECT_ID('DBA_DBGrowth') IS NULL
 BEGIN
     SELECT TOP 0 * INTO DBA_DBGrowth
     FROM sys.database_files   

     ALTER TABLE DBA_DBGrowth
     ADD CreationDate DATETIME DEFAULT(GETDATE())
 END
 
The above script creates the table for the first time. Below script captures the database size for all the databases. You can create and schedule a job on regular inerval, say daily to have a treand and do the capacity planning.

 EXECUTE sp_MSforeachdb  'INSERT INTO DBA_DBGrowth SELECT *, GETDATE() FROM
[?].sys.database_files'
 
Executing below code shows you the database growth trend:

USE [DBA_DB]
GO
SELECT * FROM DBA_DBGrowth