It is much important to track the database growth as it is one of the DBA activities. Tracking database growth helps you to find the rate at which the database files are growing thus preventing the disk run out of space.
The history of backup and restore will be stored in a backset and backupfile tables on MSDB database. Whenever you backup a database the BACKUP command inserts a row in the backupset table and one row each for every file in the backed-up database in the backupfile table, along with the size of each file. Here I have used these file sizes logged by BACKUP command, compared them with the previous sizes and came up with the percentage of file growth. You must take full database backups periodically, at regular intervals to gather the details from backupset and backupfile tables.
Below is the stored procedure to track the db growth. You must create this in master database. Further you can call up the stored procedure from any of the database for which you want to find the growth percentage. There is no need to pass any parameters. If unspecified it will work with the current database.
CREATE PROC sp_track_db_growth
(
@dbnameParam sysname = NULL
)
AS
BEGIN
DECLARE @dbname sysname
SET @dbname = COALESCE(@dbnameParam, DB_NAME())
SELECT CONVERT(char, backup_start_date, 111) AS [Date],
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)/b1.file_size)-100)
FROM msdb.dbo.backupfile b1
WHERE b1.backup_set_id =
(
SELECT MAX(b2.backup_set_id)
FROM msdb.dbo.backupfile b2
JOIN msdb.dbo.backupset b3
ON b2.backup_set_id = b3.backup_set_id
WHERE b2.backup_set_id < a.backup_set_id AND
b2.file_type='C' AND
b3.database_name = @dbname AND
b2.logical_name = a.logical_name AND
b2.logical_name = b1.logical_name AND
b3.type = 'C') AND
b1.file_type = 'C') 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 = 'C' AND
b.type = 'C'
)
As Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]
END
You will get the output columns as,
Date | Time | Database Name | Growth Percentage (%) | Filegroup Name | Logical Filename | Physical Filename | File Size (MB) |
File Size (MB) Column specifies the size of that particular file on particular date and time.
Growth Percentage (%) Column specifies the rate at which the file has grown, since the previous full-database backup. A negative value in this column specifies that the file reduced in size since the last backup, probably because of database shrinking. A NULL value column indicates that there is no previous information available, to match up with.
