mssqltips logo

How to Check Monthly Growth of Database in SQL Server

By:   |   Updated: 2019-09-05   |   Comments (5)   |   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:

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.

--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.


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

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

Hello,

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

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.

Regards 


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

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools