Stored procedure to check MS SQL database size growth

The figure of Database file growth is very important for a database administrator to check. Many hosts restrict the size of MS SQL database for their clients. In such case, database administrator need to constantly check the file growth of his/her database. I found one useful stored procedure which will provide the file growth information of a particular database. It will display database file growth in percentage(%) value and also with following results:

 

Date

Time

Database Name

Filegroup Name

Logical Filename

Physical Filename

File Size (MB)

Growth Percentage (%)

 

 

=========================================

 

Stored procedure sp_track_db_growth:

 

==========================================

 

DECLARE @dbname sysname

/* Work with current database if a database name is not specified */

SET @dbname = COALESCE(@dbnameParam, DB_NAME())

SELECT	CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
	CONVERT(char, backup_start_date, 108) AS [Time],
	@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name], logical_name AS [Logical Filename], 
	physical_name AS [Physical Filename], CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],
	Growth AS [Growth Percentage (%)]
FROM
(
	SELECT	b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name,
		(
			SELECT	CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
			FROM	msdb.dbo.backupfile i1
			WHERE 	i1.backup_set_id = 
						(
							SELECT	MAX(i2.backup_set_id) 
							FROM	msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
								ON i2.backup_set_id = i3.backup_set_id
							WHERE	i2.backup_set_id < a.backup_set_id AND 
								i2.file_type=\'D\' AND
								i3.database_name = @dbname AND
								i2.logical_name = a.logical_name AND
								i2.logical_name = i1.logical_name AND
								i3.type = \'D\'
						) AND
				i1.file_type = \'D\' 
		) AS Growth
	FROM	msdb.dbo.backupfile a JOIN msdb.dbo.backupset b 
		ON a.backup_set_id = b.backup_set_id
	WHERE	b.database_name = @dbname AND
		a.file_type = \'D\' AND
		b.type = \'D\'
		
) as Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]

END

 

 

Once you copy this stored procedure in database, you need to execute below example commands:

 

Example 1:

 

To see the file growth information of the current database:

 

EXEC sp_track_db_growth

 

Example 2:

 

To see the file growth information for pubs database:

 

EXEC sp_track_db_growth \'pubs\'

 

I hope this article will help database administrators to manage database file growth.

 

Thanks!