How to Check Monthly Growth of Database in SQL Server

By:   |   Updated: 2019-09-05   |   Comments (5)   |   Related: More > Database Administration


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.


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:

query result set
  • 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:

query result set
  • 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.

WITH BackupsSize AS(
      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))
    [database_name] = N'XXXX'
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 13, GETDATE()) AND GETDATE()
    , DATEPART(yyyy,[backup_start_date])
    , DATEPART(mm, [backup_start_date])
ORDER BY [Year],[Month]) 
   b.[Backup Size GB],
   0 AS deltaNormal,
   b.[Compressed Backup Size GB],
   0 AS deltaCompressed
FROM BackupsSize b
WHERE b.rn = 1
   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
   SELECT bs.[Backup Size GB],bs.[Compressed Backup Size GB]
   FROM BackupsSize bs
   WHERE bs.rn = b.rn - 1
) AS d

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.

Last Updated: 2019-09-05

get scripts

next tip button

About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources

Comments For This Article

Thursday, September 05, 2019 - 2:09:38 PM - Jeff Moden Back To Top (82260)

Nice, simple, and straightforward article.  Thanks for taking the time to write it.

Just remember that the backup size doesn't include "free space" and that can be as important and the data itself because, especially during index maintenance, "free space" will happen and if it can't be created, the index maintenance isn't going to happen.  That means that you need to include that space in any forecasting you might be doing or you're going to seriously underestimate how much disk space you actually need to support normal operations, never mind any ad hoc needs that always seem to crop up.

You also have to remember to occasionally purge backup data or the GUI's that look at the stored data will take a very long time to resolve.

Thursday, September 05, 2019 - 1:40:30 PM - Alejandro Cobar Back To Top (82259)

Hi Gina,

That table isn't wiped out after a server or instance restart, so probably you should make some research to see if you can find a job that might be purging data from this table automatically. If not, perhaps a colleague of yours might know something that can clear the air for you to confirm why is that happening.

Best regards.

Thursday, September 05, 2019 - 11:08:29 AM - Gina Hohenstatt Back To Top (82256)


I just discovered the msdb.dbo.backupset table after reading your article on viewing database growth. I was excited to have an easy tool for viewing growth over time. When I viewed entries in the table, we only data as of a date around a server restart or SQL Service restart.

I've found no documentation as to whether data in that table is lost after a server or service restart. Is the data truncated or is it possible there is some setting that controls data in this table based on a retention policy.

Thanks in advance!

Thursday, September 05, 2019 - 10:43:57 AM - Alejandro Cobar Back To Top (82254)

Hi Ahmed,

No, I'm not assuming that. That's why I'm including both fields (backup_size and compressed_backup_size) because if your database has a lot of white space in it then the backup size might be very different than the "apparent" database size.

You are right regarding the gaps due to missing backups. Now, for this part I am assuming that there is consistent information, monthly, in the MSDB.


Thursday, September 05, 2019 - 9:50:49 AM - Ahmed Back To Top (82252)

Hi and thaks for the tips

You are assuming that the size of backup is the same as database right? Also probably changing the join on Day/Month/Year is better than ROW_NUMBER() as we might have some gaps due to missing backups.

Best regards


Recommended Reading

How to rename a SQL Server database

How to determine SQL Server database transaction log usage

Different ways to determine free space for SQL Server databases and database files

How to read the SQL Server Database Transaction Log

Execute SQL Server Script Files with the sqlcmd Utility

get free sql tips
agree to terms

Learn more about SQL Server tools