Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Size 1 day backup size

Posted by Simon Cho on 03/08/2012

3rd part tool isn’t working well.

ex) Litespeed

SQL server 2000-2008R2 working fine.

Sum(Calculate 1 day backup set including transaction log)

use msdb

--select top 10 * from backupfile
--select top 10 * from backupmediaset
--select top 10 * from backupmediafamily

declare @sql nvarchar(4000)
if exists(select OBJECT_NAME(id) from syscolumns where name='compressed_backup_size') begin
	set @sql = '
	select top 1 @@servername as servername
		   , case when sum(compressed_backup_size)convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end else begin
	set @sql = '
	select top 1 @@servername as servername, ''N'' compressed_backup
	, convert(numeric(10,2),sum(backup_size/1024/1024/1024.0)) as [backup_size(GB)] 
	 from msdb.dbo.backupset with(nolock)
	where backup_start_date>convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end

exec(@sql)

Advertisements

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

 
%d bloggers like this: