获取所有数据库的数据库大小趋势

Get Database Size Trending On All Databases

发布-日期: 2015年07月27日 (评论)

中文
English
SQL Logic
Build Info
Author
License

中文

这里有一些SQL逻辑，它将在备份压缩之前和之后使用数据库大小生成一个简单的报告。这个逻辑最初是在Erin Stellato的SQLSkills中创建的。原始帖子可以在这里找到：http：//www.sqlskills.com/blogs/erin/trending-database-growth-from-backups/她指出如何通过excel轻松地对这些数据进行趋势分析。我只是修改了逻辑以便可以包含所有数据库，并检查数据库是否仍然存在。

English

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.

Logic

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)