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 =
'

Hi DBA Team,

Below is the Database File Growth Report of ' + @InstanceName + ' Production Server Databases from the last 3 months with more than 10% Change Rate :