By: Alejandro Cobar | Comments (7) | Related: More > Database Administration
Problem
At some point you are probably going to be asked how quickly your SQL Server database is growing. You could try to create a report based on auto growth's that occurred, but if the data file was initially set large enough no auto growths would be triggered. The T-SQL script in this tip will attempt to give you a pretty good answer to address the growth of the database by looking at backup sizes.
Solution
In this tip I’m going to present you a T-SQL script that presents the data growth for a particular database and its delta compared to the previous month. The script will work only if you have enough historical data in your msdb database, because it makes all the calculations based on the database backup information history.
T-SQL Script Explained
The logic is based around a Common Table Expression so that in can later be used in a CROSS APPLY to calculate the deltas for each month.
SECTION 1 of Code
The Common Table Expression (called BackupsSize) contains the result set of the data for the specified database, for the specified previous X months. If executed on its own, it will output data like this:
- As you can see, I have included both the regular backup size and the compressed backup size, so you can use either of these values.
- The backup type used is full.
- The rn column will be used in the CROSS APPLY to obtain the deltas for each backup size column.
- Please replace XXXX with the database name that you want to target.
- In the WHERE clause, inside the DATEADD function, please specify the number of months that you would like to fetch the respective information from the MSDB database. The script below has it set to 13 months.
- Both the regular backup size and compressed backup size are an average of the information within each month.
SECTION 2 of Code
After executing the script, as a whole, the final result set will look like this:
- As you can see, there’s now a delta column for each type of backup size, 1 for regular and 1 for compressed.
- To achieve this, in the CROSS APPLY section we select the current row evaluated in the "secondary table" against the previous row of the Common Table Expression.
- You will notice that there’s a UNION used, but it is only to include the very first row of the Common Table Expression (as the base case) because the first row doesn’t have any other row to compare against. Therefore, it is explicitly included as the first row of the result set without any calculation being made for it.
T-SQL Script to Show Database Growth Based on Backup Sizes
Change the 'XXXX' to your database name prior to running.
--SECTION 1 BEGIN
WITH BackupsSize AS(
SELECT TOP 1000
rn = ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC)
, [Year] = DATEPART(year,[backup_start_date])
, [Month] = DATEPART(month,[backup_start_date])
, [Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4))
, [Compressed Backup Size GB] = CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4))
FROM
msdb.dbo.backupset
WHERE
[database_name] = N'XXXX'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
GROUP BY
[database_name]
, DATEPART(yyyy,[backup_start_date])
, DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month])
--SECTION 1 END
--SECTION 2 BEGIN
SELECT
b.Year,
b.Month,
b.[Backup Size GB],
0 AS deltaNormal,
b.[Compressed Backup Size GB],
0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
UNION
SELECT
b.Year,
b.Month,
b.[Backup Size GB],
b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal,
b.[Compressed Backup Size GB],
b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed
FROM BackupsSize b
CROSS APPLY (
SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
FROM BackupsSize bs
WHERE bs.rn = b.rn - 1
) AS d
--SECTION 2 END
With this information, you can give a pretty good idea to your customer about an average data growth, month by month for the past X months.
Next Steps
- If you would like to dig deeper on the CROSS APPLY function, here’s a resource you can look into:
- If you’d like to, you can probably add a week column in the result set to have a growth trend by week or even daily if your case requires it.
- Remember that this approach is a bit useless if the backupset table from the msdb is being purged with certain frequency or if simply there is not enough data to evaluate.
- To make things interesting, you probably can create a Table Valued Function with the T-SQL presented in this tip and use it in any report that you have been asked to deliver.
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
