How do we find the size of a database over time in SQL Server?
Certifications:
Very often we will need to make an estimate of the space that a database will need in the future SQL Server. To do this we need to know the growth rate of the space used by the database.
Where can we find the information?
Everything we need is in the system base msdb and more specifically in the table backupset. In the table there is the field backup_size containing the base size and the compressed_backup_size which is the size that compressed backup that was taken.
This information is kept every time we receive backup a base. By default the information is in msdb it doesn't turn off but it is kept forever. To turn off or run one maintenance plan with cleanup task or the procedure msdb.dbo.sp_delete_backuphistory.
How does it work
With the following query we can get the size of the database each time the database was backed up. This is how we can make our assessment over time.
*Removing the comment from the and field msdb.dbo.backupset.database_name = 'db_test' we can choose only specific base:
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) as Server_Name,
msdb.dbo.backupset.database_name,
cast((msdb.dbo.backupset.backup_size/1024/1024) as int) as size_in_MB,
cast((msdb.dbo.backupset.backup_size/1024/1024/1024) as int) as size_in_GB,
DATEPART(year,[backup_start_date]) as Year,
DATEPART(month,[backup_start_date]) as Month,
DATEPART(DAY,[backup_start_date]) as Day,
msdb.dbo.backupset.backup_start_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE 1=1
--and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) between GETDATE()-359 and GETDATE()-365)
and msdb.dbo.backupset.type ='D'
--and msdb.dbo.backupset.database_name = 'db_test'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date desc
How will we be able to see the size of the base a year ago
If we uncomment the following code in the where statement we can see only the entries for the backups taken in the week a whole year ago:
and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) between GETDATE()-359 and GETDATE()-365)
