Here’s some SQL logic that will use the database size before and after backup compression and produce a simple report. This logic was originally created at SQLSkills from Erin Stellato. The original post can be found here: http://www.sqlskills.com/blogs/erin/trending-database-growth-from-backups/ She points out how you can easily trend this data via excel.
I simply modified the logic to include all databases, and to check to see if the database still exists.
use master; set nocount on declare @get_dbsize_history varchar(max) declare @first_day_of_year varchar(25) set @first_day_of_year = (select dateadd(year, datediff(year, 0, getdate()), 0)) set @get_dbsize_history = '' select @get_dbsize_history = @get_dbsize_history + ' select ''database'' = cast([database_name] as varchar(20)) , ''month'' = convert(varchar(7),[backup_start_date],120) , ''backup size gb'' = str(avg([backup_size]/1024/1024/1024),5,2) , ''compressed bu size'' = str(avg([compressed_backup_size]/1024/1024/1024),5,2) , ''compression ratio'' = str(avg([backup_size]/[compressed_backup_size]),5,2) from msdb.dbo.backupset where [database_name] = ''' + name + ''' and [type] = ''d'' and backup_finish_date > ''' + @first_day_of_year + ''' and database_name in (select name from master.sys.databases) group by [database_name] , convert(varchar(7),[backup_start_date],120) order by [database_name],convert(varchar(7),[backup_start_date],120);' + char(10) + char(10) from sys.databases where database_id > 4 exec (@get_dbsize_history)
This information can then be trended using excel. For example; this is one of the Sharepoint Databases.
Categories: SQL Stuff