SQL Database Growth Report Day wise report

Question

SQL database growth report
How to Generate SQL Database Growth Report Day wise report.
SQL Server 2016 SE

Answer 1

Hi @Devendra Kumar Sahu ,

We can get the information from backup history, as Olaf mentioned in your previous thread.

If you do not want to get the information from backup history. Suggest you following below steps.

1. Create a database ‘REPORT_DB’ to store daily tracked database growth data. 2.. Run the below script to collect current database size & store in table ‘DBSizeDailyReport’ within ‘REPORT_DB’ database. USE [REPORT_DB]
   GO
   IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DBSizeDailyReport')
   BEGIN; CREATE TABLE [dbo].[DBSizeDailyReport](
   [ServerName] nvarchar NOT NULL,
   [DbName] nvarchar NOT NULL,
   [SizeInMB] [int] NOT NULL,
   [WeekID] [int] NOT NULL,
   [Date] [datetime] NOT NULL
   ) ON [PRIMARY] CREATE CLUSTERED INDEX [IXC_DBSizeDailyReport_date] ON [dbo].[DBSizeDailyReport] ([date]); END
   GO DECLARE @todaydate DATE,
   @weekDate DATETIME,
   @weekID INT
   SET @todaydate=(SELECT CONVERT(VARCHAR(10),GETDATE(),111))
   SELECT @weekDate=GETDATE()
   SET @weekID= (SELECT (DATEPART(DY, @weekDate)))
   IF EXISTS (SELECT 1 FROM [DBSizeDailyReport] WHERE ServerName = @@SERVERNAME and WeekID = @weekID )
   BEGIN
   DELETE FROM [DBSizeDailyReport] Where ServerName = @@SERVERNAME and WeekID = @weekID
   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 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

3.. To run the main script daily we may create a job & schedule the job.

So, after a week 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.

Refer to the blog Calculate database size ‘Growth Rates’ on daily basis in SQL Server to get detail steps.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.