November 16, 2009 at 1:40 pm
We learned today that our backups were not working. Fortunately, we didn't have any database failures along the way.
We have a "locked down" shop and do not have access to the backups themselves, or any logs created when they ran/failed. Is there a way to query msdb to determine if a backup fails? I tried the following, but I don't see anything that would indicate a failure.
use msdb
select
*
from
backupset
where
database_name = 'camp'
order by
backup_start_date desc
November 16, 2009 at 2:05 pm
I can't think of a way to do that directly, but you could certainly query that table and compare it to a calendar of when backups are supposed to be run, and find any missing dates. Right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2009 at 3:20 pm
If the job is failing, you should configure it to send you an email notification.
I think failed jobs would be in job history, not backup history.
To double check your current situation, you can run something like this:
-- Databases NOT Backed Up Since YYYY-MM-DD
select * from master..sysdatabases db
where (not exists
(select * from msdb.dbo.backupset
where db.name = msdb.dbo.backupset.database_name
and backup_start_date > '2009-11-15'))
order by name
Or this to see when the most recent FULL, DIFF or LOG backup was for each database.
-- Most Recent Backups and # of days since ANY type of backup
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
case
when status > 16 then 'Check DB Status' -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
where B.name not like '%skip these%'
GROUP BY B.name , a.type, status
ORDER BY B.name , LastBackupDate desc,a.type, status
Here's some code I had for finding failed jobs:
-- Find any failed job for today's date
SELECT 'Job: ' + Job.[name], Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time],'Hist', hst.*
FROM [msdb].dbo.sysjobhistory Hst
INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]
where hst.run_status = '0' -- 0 = FAILED
and convert(varchar(8), GETDATE(),112)= Hst.[run_date]
ORDER BY Job.[name],Hst.[run_date] DESC, Hst.[run_time] DESC
They're all old 2000 code but will work in 2005.
November 16, 2009 at 6:22 pm
homebrew01 (11/16/2009)
If the job is failing, you should configure it to send you an email notification.
In a perfect world, I would agree with you on this point. However, our site has very unreliable email. Just because a job sends an email failure message, does not necessarily mean that it will be delivered in a timely manner, and it may not get delivered at all... Sigh.
Thanks for the query suggestions. I'll check them out.
November 17, 2009 at 7:19 am
Or this to see when the most recent FULL, DIFF or LOG backup was for each database.
.
.
.
Here's some code I had for finding failed jobs:
.
.
.
Thanks, this helps a lot!
November 17, 2009 at 7:30 am
This may help you...
/**** Below query can be used to directly pull the failed job on a
particular day and other relevant information. It can be modified to
match the exact requirement ****/
SELECT sj.[name],
sh.step_id,
sh.step_name,
sh.run_date,
sh.run_time,
sh.sql_severity,
sh.message,
sh.server
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh
ON sh.job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobsteps ss
ON sj.job_id = ss.job_id
AND sh.step_id = ss.step_id
WHERE sh.run_status = 0 /*Failure-0, Success-1*/
AND sh.run_date > 'Provide Date here'
ORDER BY sh.instance_id ASC
Source : http://technotes.towardsjob.com/sql-server/troubleshooting-sql-server-2005-jobs-failure/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply