How to Monitor SQL Server Database Growth using TSQL and Excel

Monitoring database growth is a key factor in planning a server’s resource. Use this script and create easy to understand database growth report

Why You Need to Monitor Database Growth

If you are a SQL Database administrator and if you are not having “SQL Server database capacity planning” in your key task list, then you can be sure that databases on your server will fill up your disk space pretty soon. Tracking the size and growth of SQL databases is one of the primary task of Capacity planning. This also ensures that there is enough space on disk for your databases to grow.

DIY via SQL Script and Excel

In this article, we will use SQL and Excel to capture the size of SQL databases along with the pattern. This will enable us to plan for Imminent space requirements and also help us understand the timeline during which there is a heavy volume.

This process is subdivided into four different steps so that you would be able to follow without any trouble.

Step 1: Execute this script on a new query window.

The output will have database names as row headers and Month as Column headers. Values shown in the output are database size in GB.

declare @v_count as integer, @do_count as integer, @v_month as varchar(20),@sql as varchar(5000)

create table [t_databases]
Database_Name varchar(200),
January       float(3),
February      float(3),
March  float(3),
April  float(3),
May    float(3),
June   float(3),
July   float(3),
August float(3),
September     float(3),
October       float(3),
November      float(3),
December      float(3),
create table t_databases_gateway
Database_Name varchar(200),
Database_Size float(3)
set @v_count = (select COUNT(*) from t_databases ) 
if @v_count = 0 
insert into t_databases(Database_Name)
select name from sysdatabases 
if @v_count <> 0 
--this script captures the size of all databases. You can add a where clause to capture the size of specific database name 
 INSERT t_databases (Database_Name) 
 SELECT DISTINCT Name FROM sysdatabases cr LEFT JOIN t_databases c ON cr.Name = c.Database_Name WHERE c.Database_Name IS NULL 
 --select * from master.dbo.t_databases 
 --drop table t_databases 
 set @do_count = 1 
 while (@do_count <=12) 
 set @v_month = DATENAME(m, str(@do_count) + '/1/2016') –change the year to 2017 or other year as per your requirement 
 truncate table master.dbo.t_databases_gateway 
 insert into master.dbo.t_databases_gateway select distinct
from msdb.dbo.backupset inner join master.dbo.t_databases on msdb.dbo.backupset.[database_name] = master.dbo.t_databases.[Database_Name] where
datepart(m,msdb.dbo.backupset.[backup_finish_date]) =  @do_count and datepart(yyyy,msdb.dbo.backupset.[backup_finish_date] ) = 2015 group by msdb.dbo.backupset.[database_name]
set @sql = 'update master.dbo.t_databases set ' + @v_month + ' = (select Database_Size from master.dbo.t_databases_gateway where master.dbo.t_databases.Database_Name = master.dbo.t_databases_gateway.Database_Name)'
exec (@sql)
set @do_count = @do_count + 1
select * from t_databases
drop table t_databases_gateway
drop table t_databases

Output :Output Database Size

Step 2: Create a new instance of Excel, and copy the Output from previous step into your new Excel sheet.

Copy The Output Into Excel Sheet

Step 3: Now select first two rows on the sheet and insert a 2D line chart.

This will create the trend chart for the selected database. In the Chart, X Axis will denote months and Y Axis will denote the database size in GB.Select First Two Rows On The Sheet And Insert A 2D Line Chart

Step 4: Select the entire data by dragging the ‘plus’ symbol to envelop it.

This will show the trend of all databases on the chart.Select The Entire Data

Show The Trend Of All Databases On The Chart

If you skip Step 3 and insert a 2D line chart by selecting the entire table, you will still get a chart but the X axis will denote Database names instead of Month,This is not the required output.Skip Step 3 And Insert A 2D Line Chart By Selecting The Entire Table

The Output table might have some NULL values. This happens because the database backup history might not have any record for the  database for that particular month. It also implies that the script refers to the backup size in the backup history table to track the growth trend. If any of your databases is not included in the backup plan, the Output table will still hold the database name but values for all months will be NULL

Fix Oversized Database

If you do not follow the above strategy in the past, and your database is oversized, then it may cause various problems. In such a case, you’d better to find a SQL Server file recovery tool to solve the problem effectively and efficiently.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook problem and excel recovery software products. For more information visit

4 responses to “How to Monitor SQL Server Database Growth using TSQL and Excel”

  1. I just could not leave your site before suggesting that I extremely loved the standard information a person provide for your visitors? Is going to be again regularly in order to investigate cross-check new posts

  2. Its such a shame i cant get this to work for my 2012 system.
    Ive tried looking through things – having change the year parameter to 2021.
    Im receiving null against all results.

  3. Incredible news, Generally I never remark on online journals yet your article is persuading to the point that I never stop myself to say something concerning it. You’re working feasibly, Keep it up. You can see this article, may be of help 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *