Database File Growth Report Script
Growth in database size can fill up your storage and create serious production downtime if not monitored properly. Below scripts allows DBA's to keep track and monitor the growth of Database Files size.
It works in following sequence-
- Collects the size of Database Files(Log and Data) on daily basis through a stored procedure and insert the details into a table.
- Collects the data from the table and compares the growth in database files in last three months. Then it sends the report to the DBA Team if any file grows more than 10% in last 3 months.
Note - You can modify the scripts and change some parameters as per you requirement.
USE [DBUtility]
--Step 1: Table to store database files size on daily basis
CREATE TABLE [DBUtility].[dbo].[tbl_DatabaseFileSpace](
[InstanceName] [nvarchar](50) NULL,
[DatabaseName] [nvarchar](50) NULL,
[FileName] [nvarchar](50) NULL,
[FileType] [nvarchar](20) NULL,
[TotalSizeMB] [decimal](10, 2) NULL,
[FreeSpaceMB] [decimal](10, 2) NULL,
[PercentFreeSpace] [decimal](10, 2) NULL,
[AutoGrowthMB] [decimal](10, 2) NULL,
[MaxSizeMB] [nvarchar](50) NULL,
[DataUpdatedOn] [datetime] NULL
) ON [PRIMARY]
GO
--Step 2: Stored Procedure to collect database files size on daily basis
CREATE OR ALTER PROCEDURE [dbo].[sp_CollectDBFileSize]
AS
BEGIN
DECLARE @db NVARCHAR(50)
DECLARE @sql NVARCHAR(max)
DECLARE dbsize CURSOR
FOR SELECT NAME from sys.databases WHERE NAME NOT IN('master','model','msdb','tempdb') and state = 0
OPEN dbsize
FETCH NEXT FROM dbsize INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'use ' + @db + '
SELECT CONVERT(NVARCHAR,SERVERPROPERTY(''servername'')), DB_NAME() AS DbName, name AS FileName,type_desc AS FileType,
cast(size/128.0 as decimal(10,2)) AS TotalSizeMB,
cast((size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) as decimal(10,2)) AS FreeSpaceMB,
cast((cast((size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) as decimal(10,2))/ cast(size/128.0 as decimal(10,2))) *100 as decimal(10,2))
as PercentFreeSpace,
cast(growth/128.0 as decimal(10,1)) as AutoGrowthMB,
case
when max_size = -1 then ''Unlimited''
else cast(cast(max_size/128.0 as decimal(10,2)) as varchar)
end as [MaximumSizeMB]
FROM sys.database_files'
--print @sql
INSERT INTO [DBUtility].[dbo].[tbl_DatabaseFileSpace]([InstanceName],[DatabaseName],[FileName],[FileType],[TotalSizeMB],[FreeSpaceMB],[PercentFreeSpace],[AutoGrowthMB],
[MaxSizeMB]) exec sp_executesql @sql
FETCH NEXT FROM dbsize INTO @db
END
CLOSE dbsize
DEALLOCATE dbsize
END
GO
--Step 3: Stored Procedure to send email to the concerned stakeholder
CREATE OR ALTER PROCEDURE [dbo].[sp_DBFileSizeTracker]
@InstanceName SYSNAME, -- SQL Instance
@EmailRecipients NVARCHAR(255), -- DBA Team's email id
@DBProfileName NVARCHAR(20) -- SQL Instance Database mail profile
AS
IF OBJECT_ID('tempdb..#MonthlyFileSizeHistory') IS NOT NULL
DROP TABLE #MonthlyFileSizeHistory;
CREATE TABLE #MonthlyFileSizeHistory (
InstanceName SYSNAME,
DatabaseName SYSNAME,
FileName NVARCHAR(128),
FileType NVARCHAR(128),
FirstMonth NVARCHAR(128),
FirstMonthSize DECIMAL(18, 2),-- Max file size in GB
ThirdMonth NVARCHAR(128),
ThirdMonthSize DECIMAL(18,2),
PercentageChange DECIMAL(18, 2) -- Percentage change
);
-- CTE to calculate max file size per month
WITH MonthlyMax AS (
SELECT
InstanceName,
DatabaseName,
FileName,
CASE
WHEN FileType = 'ROWS' THEN 'Data'
WHEN FileType = 'LOG' THEN 'Log'
-- ELSE NULL
END AS FileType,
FORMAT(DataUpdatedOn, 'yyyy-MM') AS Month,
CONVERT(DECIMAL(10, 2), MAX(totalsizemb / 1024.0)) AS MaxFileSize -- Convert MB to GB
FROM [DBUtility].[dbo].[tbl_DatabaseFileSpace]
WHERE InstanceName = @InstanceName --AND FileType = @FileType
AND DataUpdatedOn >= DATEADD(MONTH, -2, GETDATE())
GROUP BY InstanceName, DatabaseName, FileName, FileType, FORMAT(DataUpdatedOn, 'yyyy-MM')
),
FirstAndThird AS (
SELECT
InstanceName,
DatabaseName,
FileName,
FileType,
Month,
MaxFileSize,
ROW_NUMBER() OVER (PARTITION BY DatabaseName, FileName ORDER BY Month) AS RowNum
FROM MonthlyMax
)
INSERT INTO #MonthlyFileSizeHistory
SELECT
F1.InstanceName,
F1.DatabaseName,
F1.FileName,
F1.FileType,
F1.Month AS FirstMonth,
F1.MaxFileSize AS FirstMonthSize,
F3.Month AS ThirdMonth,
F3.MaxFileSize AS ThirdMonthSize,
CONVERT(DECIMAL(10, 2), ((F3.MaxFileSize - F1.MaxFileSize) * 100.0) / F1.MaxFileSize) AS PercentageChange
FROM FirstAndThird F1
JOIN FirstAndThird F3 ON
F1.DatabaseName = F3.DatabaseName
AND F1.FileName = F3.FileName
AND F1.RowNum = 1 -- First month
AND F3.RowNum = 3 -- Third month
ORDER BY F1.DatabaseName, F1.FileName;
DECLARE @EmailBody NVARCHAR(MAX);
DECLARE @EmailSubject NVARCHAR(255) = 'Database File Growth Report from last 3 Months for ' + @InstanceName;
-- Generate HTML table with the report
SET @EmailBody =
'<html>
<body>
<p>Hi DBA Team,</p>
<p>Below is the Database File Growth Report of <b>' + @InstanceName + '</b> Production Server Databases from the last 3 months with more than 10% Change Rate :</p>
<table border="1" style="border-collapse: collapse; width: 80%;">
<tr style="background-color: #f2f2f2; text-align: left;">
<th style="padding: 8px;">SQL Server Name</th>
<th style="padding: 8px;">Database Name</th>
<th style="padding: 8px;">File Name</th>
<th style="padding: 8px;">File Type</th>
<th style="padding: 8px;">First Month</th>
<th style="padding: 8px;">First Month Size (GB)</th>
<th style="padding: 8px;">Third Month</th>
<th style="padding: 8px;">Third Month Size (GB)</th>
<th style="padding: 8px;">Percent Change</th>
</tr>';
DECLARE @Row NVARCHAR(MAX);
SET @Row = '';
SELECT @Row +=
'<tr>
<td style="padding: 8px;">' + InstanceName + '</td>
<td style="padding: 8px;">' + DatabaseName + '</td>
<td style="padding: 8px;">' + FileName + '</td>
<td style="padding: 8px;">' + ISNULL(FileType,'N/A') + '</td>
<td style="padding: 8px;">' + FirstMonth + '</td>
<td style="padding: 8px;">' + CONVERT(NVARCHAR(50), FirstMonthSize) + '</td>
<td style="padding: 8px;">' + ThirdMonth + '</td>
<td style="padding: 8px;">' + CONVERT(NVARCHAR(50), ThirdMonthSize) + '</td>
<td style="padding: 8px;">' + ISNULL(CONVERT(NVARCHAR(50), PercentageChange), 'N/A') + '</td>
</tr>'
FROM #MonthlyFileSizeHistory
WHERE FileName IN (SELECT FileName from #MonthlyFileSizeHistory where PercentageChange > 10.0)
ORDER BY DatabaseName, FileName--, Month;
-- Close the HTML table
SET @EmailBody = @EmailBody + @Row + '</table></body></html>';
IF((SELECT COUNT(*) FROM #MonthlyFileSizeHistory WHERE FileName IN (SELECT FileName from #MonthlyFileSizeHistory where PercentageChange > 10.0)) > 0)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @DBProfileName,
@recipients = @EmailRecipients,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML';
END
DROP TABLE #MonthlyFileSizeHistory; 
