How to track database file growth over a period of time?
Last updated: December 8th '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

In this article I am going to explain, how to track file growths, especially the database files. First of all, why is it important to track database file growth? Tracking file growth, helps you understand the rate at which your database is growing, so that you can plan ahead for your future storage needs. It is better to plan ahead, instead of running around when you run out of disk space, isn't it? So, how can we track file growths? There are a couple of ways.

The first approach:
SQL Server BACKUP and RESTORE commands store the backup, restore history in the msdb database. In this approach, I am going to use the tables backupset and backupfile from msdb to calculate the file growth percentages. Whenever you backup a database the BACKUP command inserts a row in the backupset table and one row each for every file in the backed-up database in the backupfile table, along with the size of each file. I am going to use these file sizes recorded by BACKUP command, compare them with the previous sizes and come up with the percentage of file growth. This approach assumes that you do full database backups periodically, at regular intervals.

Click here to download the procedure sp_track_db_growth. Run the script in your master database to create the procedure. Once it is created, you can invoke it from any of your user databases. It accepts an optional parameter @dbnameParam. If this parameter is not specified, the procedure will work with the current database. Here is how you use it:

Example 1:
To see the file growth information of the current database:
EXEC sp_track_db_growth
GO

Example 2:
To see the file growth information for pubs database:
EXEC sp_track_db_growth 'pubs'
GO

You will see the following columns in the output:

Most of the column names are self-explanatory. The columns of interest are 'File Size (MB)' and 'Growth Percentage (%)'. The column 'File Size (MB)' indicates the size of that particular file on that particular date and time. The column 'Growth Percentage (%)' indicates the rate at which the file grew, since the previous full-database backup. A negative number in this column indicates that the file reduced in size since the last backup, possibly because of database shrinking. A NULL in this column indicates that there is no previous information available, to compare with.

This procedure might run a little slow if your backup history tables are huge. You might want to cleanup these backup history tables from msdb once in a while, by running sp_delete_backuphistory. See SQL Server Books Online for more information.

Any comments, suggestions and feedback is welcome! Let me know how this stored procedure performs in your environment! 

The second approach:
SQL Server 2000 now supports new events like Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, Log File Auto Shrink. You might want to monitor these events by using the trace stored procedures. Consult SQL Server Books Online for more information on trace stored procedures.

The third approach:
The sysfiles system table in each database stores the current size of each file in the database. You could periodically monitor the file sizes from this table and record them in another table for further calculations and analysis. Mark Allison has some useful scripts at his site. Check it out!