Cool Things Your MSDB Database Will Tell You (If You Ask) #sqlsat79

2

August 13, 2011 by Mike Hillwig

Another presentation I’m giving at SQL Saturday 79 is Cool Things Your MSDB Database Will Tell You (If You Ask). This is a new presentation for me, and it’s different from my other two in that it’s less heavy on the Power Point and more heavy on the demonstrations.  This blog post contains the scripts I use in my presentation.

žJob Schedule
-- sysschedules contains a lot of great details. 
-- Check out the BOL page for more details
-- Take note that when I create a schedule, I always
-- make the name of the schedule correspond to the
-- actual schedule.
select sj.name, ss.name, ss.*
from msdb.dbo.sysjobs sj
inner join msdb.dbo.sysjobschedules sjs
on sj.job_id = sjs.job_id
inner join msdb.dbo.sysschedules ss
on sjs.schedule_id = ss.schedule_id
order by sj.name
žJob History

 


select sjh.server,sj.name, sj.description, sjs.step_name, sjh.message,
sjh.run_status, sjh.run_date, sjh.run_time,
sjh.run_duration
from sysjobs sj
inner join sysjobsteps sjs
on sj.job_id = sjs.job_id
inner join sysjobhistory sjh
on sjs.job_id = sjh.job_id
and sjs.step_id = sjh.step_id
order by sjh.server, sj.name, sjs.step_name, run_date, run_time
žFailed Jobs/Steps Report
ž
--The code to convert run_date and run_time into an actual
--datetime is NOT my code, and I can't remember what blog post
--I found it from. But it's not mine.
select sj.name, sjs.step_name,
CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6
       + run_time % 100 * 10) / 216e4
from msdb.dbo.sysjobs sj
inner join msdb.dbo.sysjobsteps sjs
on sj.job_id = sjs.job_id
inner join msdb.dbo.sysjobhistory sjh
on sjs.job_id = sjh.job_id
and sjs.step_id = sjh.step_id
where sjh.run_status = 0
and datediff (hour, (CONVERT ( DATETIME, RTRIM(run_date))+(run_time * 9+ run_time % 10000 * 6
       + run_time % 100 * 10) / 216e4), current_timestamp) <= 24
Backup History
žDatabases Not Fully Backed Up In the Past X Hours
DECLARE @HoursToCheck INT
SELECT @HoursToCheck = 24
SELECT      sd.name, max(bs.backup_finish_date)
FROM        master.sys.databases sd
LEFT JOIN   msdb.dbo.backupset bs
ON          bs.database_name = sd.name
where       ISNULL(type,'D') = 'D'
-- Replace D with L for TLog Backups
AND                     sd.name <> 'TEMPDB'
GROUP BY    sd.name
HAVING      max(backup_finish_date) IS NULL
OR          datediff(hour,max(backup_finish_date), getdate()) >= @HoursToCheck
order by    sd.name
žMail Sent
select mp.name profile, mi.mailitem_id, mi.recipients, mi.copy_recipients, mi.blind_copy_recipients,
mi.subject, mi.body
from msdb.dbo.sysmail_mailitems mi
inner join msdb.dbo.sysmail_profile mp
on mi.profile_id = mp.profile_id

Things Your MSDB Database Can Tell You
  • Ingrid Olivero

    Got Them! Implementing some of these for my daily poroduction monitoring. Thanks!

  • Cool Scripts ..thanks for providing …