How do we find the size of a database over time in SQL Server?

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

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)

Sources:

• backupset (Transact-SQL)