Find database growth size and store in a table in SQL Server

Introduction

Managing database sizes is a crucial task for database administrators. Monitoring database growth and tracking size changes over time are essential for capacity planning and performance optimization. In this blog post, we will explore an automated solution using T-SQL to track the daily size of databases and store the information in a dedicated table. This approach provides insights into database growth patterns and facilitates proactive decision-making.

Database creation

Use below TSQL script or SSMS and create a database ‘REPORT_DB’ to store daily tracked database growth data.

CREATE DATABASE REPORT_DB;

SQL

Creating table in database and inserting size information

Now run the below script to create table and collect current database size & store in table ‘DBSizeDailyReport’ within ‘REPORT_DB’ database.

-- Use the REPORT_DB database
USE [REPORT_DB]
GO

-- Check if the DBSizeDailyReport table does not exist, and create it if needed
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DBSizeDailyReport')
BEGIN
    -- Create the DBSizeDailyReport table
    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 a clustered index on the 'date' column for faster access
    CREATE CLUSTERED INDEX [IXC_DBSizeDailyReport_date] ON [dbo].[DBSizeDailyReport] ([date]);
END
GO

-- Declare variables for today's date, week date, and week ID
DECLARE @todaydate DATE,
        @weekDate DATETIME,
        @weekID INT

-- Set the value of @todaydate to the current date
SET @todaydate = (SELECT CONVERT(VARCHAR(10), GETDATE(), 111))

-- Set the value of @weekDate to the current date and time
SELECT @weekDate = GETDATE()

-- Set the value of @weekID to the day of the week (1-7, where 1 is Sunday)
SET @weekID = (SELECT (DATEPART(DY, @weekDate)))

-- Check if a record already exists for the current server and week in the DBSizeDailyReport table
IF EXISTS (SELECT 1 FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME AND WeekID = @weekID)
BEGIN
    -- Delete the existing record for the current server and week
    DELETE FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME AND WeekID = @weekID

    -- Insert a new record into the DBSizeDailyReport table for the current server, week, and date
    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 a new record into the DBSizeDailyReport table for the current server, week, and date
    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

SQL

Check the table

We can check the table within the REPORT_DB database by executing below select statement.

select * from [REPORT_DB].[dbo].[DBSizeDailyReport]

SQL

Job & schedule

Now to run the main script daily we may create a job & schedule the job.

So, after few days 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.