Introduction
To calculate database size ‘Growth Rates‘ on a daily basis in SQL Server we need to track database size changes every day. The best solution is to check the database size daily and compare with previous data. We may setup a job in SQL Server and gather the information daily.
Create a database
Use below TSQL script or SSMS and create a database ‘REPORT_DB’ to store daily tracked database growth data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE master ; GO CREATE DATABASE REPORT_DB ON ( NAME = REPORT_DB_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\REPORT_DB_data.mdf', -- location SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = REPORT_DB_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\REPORT_DB_log.ldf', -- Location SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) ; GO |
Database size
Now run the below script to collect current database size & store in table ‘DBSizeDailyReport’ within ‘REPORT_DB’ database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
USE [REPORT_DB] GO IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DBSizeDailyReport') BEGIN; CREATE TABLE [dbo].[DBSizeDailyReport]( [ServerName] [nvarchar](400) NOT NULL, [DbName] [nvarchar](100) NOT NULL, [SizeInMB] [int] NOT NULL, [WeekID] [int] NOT NULL, [Date] [datetime] NOT NULL ) ON [PRIMARY] CREATE CLUSTERED INDEX [IXC_DBSizeDailyReport_date] ON [dbo].[DBSizeDailyReport] ([date]); END GO DECLARE @todaydate DATE, @weekDate DATETIME, @weekID INT SET @todaydate=(SELECT CONVERT(VARCHAR(10),GETDATE(),111)) SELECT @weekDate=GETDATE() SET @weekID= (SELECT (DATEPART(DY, @weekDate))) IF EXISTS (SELECT 1 FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME and WeekID = @weekID ) BEGIN DELETE FROM [DBSizeDailyReport] Where ServerName = @@SERVERNAME and WeekID = @weekID INSERT INTO [DBSizeDailyReport] SELECT @@SERVERNAME as ServerName,d.name as DBName,ROUND(SUM(mf.size) / 1024 * 8,0) as Size_MB,@weekID,@todaydate FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.Name not in ('Master','Model','Msdb','TempDb')and mf.type=0 GROUP BY d.name ORDER BY d.name END ELSE BEGIN INSERT INTO [DBSizeDailyReport] SELECT @@SERVERNAME as ServerName,d.name as DBName,ROUND(SUM(mf.size) / 1024 * 8,0) as Size_MB,@weekID,@todaydate FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.Name not in ('Master','Model','Msdb','TempDb')and mf.type=0 GROUP BY d.name ORDER BY d.name END GO |
Check the table
We can check the table within the REPORT_DB database.
If we run select * from DBSizeDailyReport in the REPORT_DB database below result will be shown.
Job & schedule
Now to run the main script daily we may create a job & schedule the job.
So, after a week or month if we check the table we will get the individual day database size. Simply we can compare the date and calculate the database growth rate easily.
# If you want to find database growth size in SQL server instantly here is another post.
1 thought on “Calculate database size ‘Growth Rates’ on daily basis in SQL Server”