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

Posted by
August 13, 2011

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



	
	
	
	
	

	
	
SQLServerPedia Syndication

	

	

	
	

    Did you enjoy this post?  Why not leave a comment below and continue the conversation, 
	or subscribe to my feed and get articles like this delivered automatically to your feed reader.
    
	
		
	
	




Comments





		
		


		
		

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






		
Comment by Ingrid Olivero on August 15, 2011 @ 9:12 am 

		





		
		


		
		

		
Cool Scripts ..thanks for providing …






		
Comment by Shiva on August 26, 2011 @ 9:45 am 

		






Sorry, the comment form is closed at this time.