Cool Things Your MSDB Database Will Tell You (If You Ask) #sqlsat79
27August 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
Category SQLServerPedia Syndication | Tags:
