SQL Stuff

Get Database Size Trending On All Databases

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.

Advertisements

Categories: SQL Stuff

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s