Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2008-04-08 : 14:56:25
|
| Guys,How can I find out "automatically" [i.e. via a query] how many times a transaction log was backed up for a specific database within the last 24 hours?Also, how can I find out which jobs are hanging?Thank you! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 15:04:13
|
| 1)This will give you when the last full backup was done and any dependent backups sincedeclare @databasename as varchar(100)set @databasename = 'Databasename' Select database_name + rtrim(case when type ='L' then space(5)+ name when type ='D' then space(1)+name when type ='I' then space(3)+name else '' end)+ space(3)+ convert(varchar, Backup_Start_date, 121)from msdb..backupsetwhere database_name = @databasenameand Backup_Start_Date >= (select max(Backup_Start_Date)from msdb..backupsetwhere database_name = @databasenameand type = 'D')order by Backup_Start_date2) Check sysjobsteps and sysjobhistory. |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2008-04-14 : 10:29:17
|
| Thanks a lot for the information. I was able to get what is needed with a slightly modified query:SELECT COUNT(*) FROM msdb.dbo.[backupset] WHERE database_name = 'DatabaseName' AND TYPE = 'L'AND Backup_Finish_Date > GetDate()-1 |
|
|
|
|
|
|
How many times transaction log backup occurred?