February 28, 2011 at 11:26 pm
how to calculate the size of the log backup file which is schedule din hourly basis .
can any one give query to find ?
March 1, 2011 at 8:51 am
--here is a query i use in my daily report this may be what your are
--looking for
select null
,sdb.name as sql_dbname
,bs.database_name
,@@servername
,backup_size
,backup_Start_date
,abs(datediff(mi,bs.backup_finish_date,bs.backup_start_date)) as Duration
,bs.name
,user_name
,first_lsn
,last_lsn
,checkpoint_lsn
,collation_name
,abs(datediff(mi,bs.backup_finish_date,getdate())) as age
,BMF.physical_device_name
from master.dbo.sysdatabases sdb with (nolock)
left outer join msdb.dbo.backupset bs with (nolock) on sdb.name=bs.database_name and bs.type='L' and convert(varchar(10),bs.backup_start_date,101) >convert(Varchar(10),getdate()-1,101)
left outer join msdb.dbo.backupmediafamily bmf on bmf.media_set_id=bs.media_set_id
join (select database_name,max(backup_finish_date) as max_finish_date --into #tmp1
from msdb.dbo.backupset bs with (nolock)
where bs.type='L' and convert(varchar(10),bs.backup_start_date,101) >convert(Varchar(10),getdate()-1,101)
group by database_name) bs2 on bs.database_name =bs2.database_name and bs.backup_finish_date=bs2.max_finish_date
order by bs.database_name,bs.backup_start_date
March 2, 2011 at 1:03 am
thanks for the reply
we created job to take log backup of the batabases on hrly basis ,
i want to find the size of log files of the two dbs which are .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply