SQL Server – Database Size Growth Summary

Here the SQL Script written for finding the Database size periodically and use it for Power BI desktop to analysis the database size growth periodically to the System admin and management department.

Create the table for storing the daily basis database size infromation.

CREATE TABLE DatabaseFileSize

(

[database_id] INT,

[file_id] INT,

[file_type_desc] NVARCHAR(120),

[name] NVARCHAR(128),

[physical_name] NVARCHAR(520),

[state_desc] NVARCHAR(120),

[size] INT,

[max_size] INT,

[growth] INT,

[is_sparse] BIT,

[is_percent_growth] BIT,

[collect_date] DATETIME

)

GO

Create a stored procedure to populate this table with the current database size information:

CREATE PROC usp_util_CollectDatabaseSize

AS

BEGIN

SET NOCOUNT ON

INSERT INTO DatabaseFileSize

(

[database_id],

[file_id],

[file_type_desc],

[name],

[physical_name],

[state_desc],

[size],

[max_size],

[growth],

[is_sparse],

[is_percent_growth],

[collect_date]

)

SELECT

[database_id],

[file_id],

[type_desc],

[name],

[physical_name],

[state_desc],

[size],

[max_size],

[growth],

[is_sparse],

[is_percent_growth],

GETDATE()

FROM sys.master_files where database_Id not in (1,2,3, 4)

SET NOCOUNT OFF

END

GO

Now we need to populate the table over a period of time, so we need to create a SQL Agent job to execute this stored procedure and schedule this job to run everyday.

USE [msdb]

GO

/****** Object:  Job [Collect_Database_Size_Info]    Script Date: 07/07/2021 10:47:43 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/07/2021 10:47:43 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Collect_Database_Size_Info’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N'[Uncategorized (Local)]’,

@owner_login_name=N’Domain\sandip’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [DBSize]    Script Date: 07/07/2021 10:47:43 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DBSize’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’EXEC usp_util_CollectDatabaseSize’,

@database_name=N’AuditDB’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’DailyDBSize’,

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20130213,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N’445fb175-517b-4221-b6b8-6c6877dc7c24′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK RANSACTION

End

Save:

GO

Now we have the information over the period of time and we can run our below query to make a report out of this data.

Track the whole database size growth summary over a period of time:

–DB File Size Growth Trend

SELECT DB_NAME(database_id) AS DatabaseName,

(CASEfile_type_desc

WHEN‘ROWS’THEN‘Data’

WHEN‘LOG’THEN‘Log’

END) AS FileType,

physical_name AS PhysicalPath,

collect_date AS CollectionDate,

((SUM(size))*8)/1024 AS FileSizeInMB

FROM DatabaseFileSize

GROUP BY database_id,physical_name,file_type_desc,collect_date

ORDER BY DatabaseName,collect_date

–DB Size Growth Trend

SELECT

DB_NAME(database_id) AS DatabaseName,

collect_date AS CollectionDate,

((SUM(size))*8)/1024 AS DBSizeInMB

GROUP BY database_id, collect_date

FROM DatabaseFileSize