Script to monitor database growth on daily basis

Question

I am fairly new to databases, I have got a specific requirement to script a Job/Stored Procedure to monitor the database growth. We are currently using MS SQL Server 2016 Standard Edition with Always-ON HA. I have setup mail server on the database. It would be even more efficient to link the output of the job to the mail.

Answer 1

There is a stored procedure called DatabaseSpaceUsage:

https://github.com/aleksey-vitsko/Database-Administrator-Tools

When you execute it exec DatabaseSpaceUsage without any parameters, it will show space usage details inside a given database

This procedure can also be called with parameter @command = 'log'

When called with @command = 'log' parameter, this SP will log result to logging table DatabaseGrowthLogger at database ServerLogsDB (DB and table need to be created)

Here is the script for creating the logging table:

USE [ServerLogsDB]
GO

/****** Object:  Table [dbo].[DatabaseGrowthLogger]    Script Date: 5/12/2020 12:11:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DatabaseGrowthLogger](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Server_Name] [varchar](50) NULL,
    [Database_Name] [varchar](250) NOT NULL,
    [Date_Full] [smalldatetime] NOT NULL,
    [Day_Of_Week] [tinyint] NULL,
    [Log_Date] [date] NULL,
    [Log_Hour] [tinyint] NULL,
    [Table_Count] [int] NULL,
    [Total_Rows] [bigint] NULL,
    [Database_Data_MB] [decimal](16, 2) NULL,
    [Database_Index_MB] [decimal](16, 2) NULL,
    [Database_Unused_MB] [decimal](16, 2) NULL,
    [Database_Allocated_MB] [int] NULL,
    [Database_File_Size_MB] [int] NULL,
    [Percent_Used] [decimal](5, 2) NULL,
    [Hours_Diff] [smallint] NULL,
    [Database_Allocated_Delta] [int] NULL,
    [Percent_Used_Delta] [decimal](5, 2) NULL,
PRIMARY KEY CLUSTERED 
(
    [Database_Name] ASC,
    [Date_Full] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Hours_Diff]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Database_Allocated_Delta]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Percent_Used_Delta]
GO

After you create ServerLogsDB..DatabaseGrowthLogger database and table, and deploy (create) stored procedure DatabaseSpaceUsage to any database (user or system), you need to schedule a job that will run once a day, for example at 23:58 UTC, and run below T-SQL:

exec DatabaseSpaceUsage @Command = 'log', @DatabaseName = 'Database1'
exec DatabaseSpaceUsage @Command = 'log', @DatabaseName = 'Database2'
...
-- in @DatabaseName parameter, supply database names of databases for which you want to monitor growth

This will log daily growth of your databases, and when you check ServerLogsDB..DatabaseGrowthLogger table, it will look similar to below:

Looking at logging table, you will have an idea how much data inside your database has grown over time

Finally, you can add a step to the job, that will read last row from ServerLogsDB..DatabaseGrowthLogger and send it by email using sp_send_dbmail procedure