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 
begin
insert into t_databases(Database_Name)
select name from sysdatabases 
 end
if @v_count <> 0 
 begin 
--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 
 end 
 --select * from master.dbo.t_databases 
 --drop table t_databases 
 set @do_count = 1 
 while (@do_count <=12) 
 begin 
 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
(msdb.dbo.backupset.[database_name]),max(msdb.dbo.backupset.[Backup_Size]/1073741824)
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
end
select * from t_databases
drop table t_databases_gateway
drop table t_databases

Output :

Step 2: Create a new instance of Excel, and copy the Output from previous step into your new 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.

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.

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.

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 www.datanumen.com