{"id":2081,"date":"2021-02-16T09:32:02","date_gmt":"2021-02-16T06:32:02","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2081"},"modified":"2024-12-03T21:05:23","modified_gmt":"2024-12-03T18:05:23","slug":"pos-vriskoyme-tin-ayxisi-toy-megethoys","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-vriskoyme-tin-ayxisi-toy-megethoys\/","title":{"rendered":"How do we find the size of a database over time in SQL Server?"},"content":{"rendered":"

Very often we will need to make an estimate of the space that a database will need in the future SQL Server<\/strong>. To do this we need to know the growth rate of the space used by the database. <\/p>\n\n\n\n

Where can we find the information?<\/h2>\n\n\n\n

Everything we need is in the system base msdb<\/strong> and more specifically in the table backupset<\/strong>. In the table there is the field backup_size <\/strong>containing the base size and the compressed_backup_size<\/strong> which is the size that compressed backup that was taken.<\/p>\n\n\n\n

This information is kept every time we receive backup <\/strong>a base. By default the information is in msdb it doesn't turn off<\/em><\/strong> but it is kept forever. To turn off or run one maintenance plan<\/em> with cleanup task <\/em>or the procedure msdb.dbo.sp_delete_backuphistory<\/strong>.<\/p>\n\n\n\n

How does it work<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

*Removing the comment from the and field msdb.dbo.backupset.database_name = 'db_test'<\/em> we can choose only specific base:<\/p>\n\n\n\n

SELECT\n   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) as Server_Name,\n   msdb.dbo.backupset.database_name,\n   cast((msdb.dbo.backupset.backup_size\/1024\/1024) as int) as  size_in_MB,\n   cast((msdb.dbo.backupset.backup_size\/1024\/1024\/1024) as int) as  size_in_GB,\n   DATEPART(year,[backup_start_date]) as Year,\n   DATEPART(month,[backup_start_date]) as Month,\n   DATEPART(DAY,[backup_start_date]) as Day,\n   msdb.dbo.backupset.backup_start_date\nFROM   msdb.dbo.backupmediafamily\n   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id\nWHERE 1=1\n--and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102)   between GETDATE()-359 and GETDATE()-365)\nand msdb.dbo.backupset.type ='D'\n--and msdb.dbo.backupset.database_name = 'db_test'\nORDER BY\n   msdb.dbo.backupset.database_name,\n   msdb.dbo.backupset.backup_finish_date desc\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
How will we be able to see the size of the base a year ago <\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102)   between GETDATE()-359 and GETDATE()-365)<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n