DBCC UPDATEUSAGE and sp_updatestats

1.Failed jobs report. This query lists the name of all jobs that failed in their last attempt:

SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

2.Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!

SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

3. Running jobs. Need to know what jobs are still running? Ever come into work on Monday morning to find several critical jobs having “piled up”, running way to long, or hung up? This query lists those running queries (whether normally scheduled or not). This procedure call is good for making sure your Distribution or Merge agent job is still running too. I make a call to sp_get_composite_job_info (loading in a bunch of NULLS, and a “1” to indicate running jobs):


msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL