question

DevendraSahu-0306 avatar image
0 Votes"
DevendraSahu-0306 asked Cathyji-msft edited

SQL Database Growth Report Day wise report

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @DevendraSahu-0306,

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](400) NOT NULL,
    [DbName] [nvarchar](100) 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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.