Capacity Planning using SQL Server Backup History Storage has always been one of the IT infrastructure component, where organization invest a lot. Optimal utilization of storage is one of the key factors on saving cost. Users should have details on the current storage requirement and also ability to forecast the tentative growth. This is critical, so that you don’t end up wasting storage by allocation extra, where the data is not growing and having trouble in adding storage where data is growing. SQL Server is used for lot of application as its database in any organization. The same storage story apply to SQL Server databases as well. When any new database is created, we may predict the storage requirement depending on may be a baseline of the applications design or just leave it to grow with know the growth. For the forecast we may use tools that may help in understanding the growth. Then there is no issue in forecasting the growth and allocating the storage accordingly. What if we don’t have any such tools in place to provide the data growth history details of database or its file? Solution: In any stable and healthy environment we maintain a good backup strategy. Let us assume that we have daily backup in place for a database called Test and we maintain the history of this backup in msdb database. This Test database have multiple data file residing on multiple drive. We can perform the below steps to build the database/data file growth report.

select a.backup_set_id,b.backup_finish_date, b.database_name, a.physical_name, sum(a.file_size/128/1024) as fizeSizeinMB from msdb..backupfile a, msdb..backupset b where a.backup_set_id = b.backup_set_id and b.database_name in ('Test') and b.type = 'D' and a.file_type = 'D' group by a.backup_set_id,b.backup_finish_date, b.database_name, a.physical_name order by b.backup_finish_date desc 2. Copy the data in excel sheet.

3. Make use of Pivot and graph to represent the data growth. We will not discuss on how to work with excel here. You can refer the excel file which will help in better understanding here.

The above easy step will provide the pattern of data file growth within a database. You can now analyze the data with slice and dice in excel.