Database Growth in % daily and Incremental
USE [DailyDatabaseGrowthReport]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DailyDatabaseGrowthReport]
AS
BEGIN
SET NOCOUNT ON;
-- Check and create the table only if it does not exist
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DBSizeDailyReport')
BEGIN
CREATE TABLE [dbo].[DBSizeDailyReport](
[ServerName] nvarchar(100) NOT NULL,
[DbName] nvarchar(100) NOT NULL,
[SizeInMB] int NOT NULL,
[WeekID] int NOT NULL,
[Date] datetime NOT NULL,
[DayWisePercentageGrowth] decimal(18, 2),
[IncrementalPercentage] decimal(18, 2)
);
CREATE CLUSTERED INDEX [IXC_DBSizeDailyReport_date] ON [dbo].[DBSizeDailyReport] ([Date]);
END;
DECLARE @todayDate DATE = CONVERT(DATE, GETDATE());
DECLARE @weekID INT = DATEPART(DY, @todayDate);
-- Remove existing data for the current week and server
IF EXISTS (SELECT 1 FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME AND WeekID = @weekID)
BEGIN
DELETE FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME AND WeekID = @weekID;
END
-- Insert new data
INSERT INTO [DBSizeDailyReport] (ServerName, DbName, SizeInMB, WeekID, Date)
SELECT
@@SERVERNAME,
d.name,
ROUND(SUM(mf.size) / 1024.0 * 8, 0),
@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;
-- Update DayWisePercentageGrowth for the week
DECLARE @MaxweekID INT = (SELECT MAX(WeekID) FROM DBSizeDailyReport);
DECLARE @weekIDCount INT = (SELECT COUNT(DISTINCT WeekID) FROM DBSizeDailyReport);
IF @weekIDCount <= 2
BEGIN
UPDATE DBSizeDailyReport
SET DayWisePercentageGrowth = 0
WHERE WeekID < @MaxweekID;
END
-- Use a single temporary table for percentage calculations
IF OBJECT_ID('tempdb..#DBSizeCalculation') IS NOT NULL
BEGIN
DROP TABLE #DBSizeCalculation;
END
SELECT *
INTO #DBSizeCalculation
FROM DBSizeDailyReport
ORDER BY WeekID;
WITH CTE AS (
SELECT
ServerName,
DbName,
WeekID,
SizeInMB,
LAG(SizeInMB) OVER (PARTITION BY ServerName, DbName ORDER BY WeekID) AS PreviousSizeInMB,
DayWisePercentageGrowth
FROM #DBSizeCalculation
)
UPDATE CTE
SET DayWisePercentageGrowth = ISNULL((SizeInMB - ISNULL(PreviousSizeInMB, 0)) * 100.0 / ISNULL(PreviousSizeInMB, 1), 0);
UPDATE O
SET O.DayWisePercentageGrowth = T.DayWisePercentageGrowth
FROM #DBSizeCalculation T
INNER JOIN DBSizeDailyReport O ON O.WeekID = T.WeekID AND O.DbName = T.DbName
WHERE O.WeekID = @weekID;
-- Total Growth Calculation
DECLARE @MinWeekID INT = (SELECT MIN(WeekID) FROM DBSizeDailyReport);
DECLARE @wkid INT;
DECLARE @MinIncrementalWeekID INT;
IF OBJECT_ID('tempdb..#DBSizeDailyReport3') IS NOT NULL
BEGIN
DROP TABLE #DBSizeDailyReport3;
END
-- Cursor to process each week
DECLARE ID_cursor CURSOR FOR
SELECT DISTINCT WeekID
FROM DBSizeDailyReport
WHERE IncrementalPercentage IS NULL;
OPEN ID_cursor;
FETCH NEXT FROM ID_cursor INTO @wkid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MinIncrementalWeekID = (SELECT IncrementalPercentage FROM DBSizeDailyReport WHERE WEEKID = @MinWeekID AND IncrementalPercentage IS NULL);
IF @MinIncrementalWeekID IS NULL
BEGIN
UPDATE DBSizeDailyReport
SET IncrementalPercentage = 0.0
WHERE WeekID = @MinWeekID;
END
SELECT *
INTO #DBSizeDailyReport3
FROM DBSizeDailyReport
WHERE WeekID IN (@MinWeekID, @wkid);
WITH CTE AS (
SELECT
ServerName,
DbName,
WeekID,
SizeInMB,
LAG(SizeInMB) OVER (PARTITION BY ServerName, DbName ORDER BY WeekID) AS PreviousSizeInMB,
IncrementalPercentage
FROM #DBSizeDailyReport3
)
UPDATE CTE
SET IncrementalPercentage = ISNULL((SizeInMB - ISNULL(PreviousSizeInMB, 0)) * 100.0 / ISNULL(PreviousSizeInMB, 1), 0);
UPDATE O
SET O.IncrementalPercentage = T.IncrementalPercentage
FROM #DBSizeDailyReport3 T
INNER JOIN DBSizeDailyReport O ON O.WeekID = T.WeekID AND O.DbName = T.DbName
WHERE O.IncrementalPercentage IS NULL;
FETCH NEXT FROM ID_cursor INTO @wkid;
END
CLOSE ID_cursor;
DEALLOCATE ID_cursor;
DROP TABLE IF EXISTS #DBSizeDailyReport3, #DBSizeCalculation;
END;
Comments
Post a Comment