SQL Database Growth Report Day wise report

Devendra Kumar Sahu 236 Reputation points
2021-09-21T06:49:43.503+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
0 comments No comments
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2021-09-21T07:34:01.077+00:00

    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.

    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful