Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When you right click on a number of items in the object explorer, it gives you the option for a reports menu. Various reports may exist depending on which type of item is selected. Yet, there does not appear to be any built-in functionality for automated delivery of these reports. It is hidden away somewhere?

My real question : Is there any way to set some of these up to be automatically delivered through email? The reports I am targeting are the "Job Steps Execution History", and "Top Jobs" reports for SQL Server agent. I am using SQL Server 2005/2008

share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 22:01

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

No, there is no scheduling capability in SSMS for the SSMS reports. This is by design, if the limitations stated here apply to standard reports too (SQL Server Manageability Team Blog)

However, (I haven't tried this) the reports are available to use in a full SSRS install. See another page "on the same blog as above", attached zip file

share|improve this answer

I found this forum entry: http://www.sqlservercentral.com/Forums/Topic434103-149-1.aspx

I saw a link to a download for the SQL 2005 reports .rdl files here: http://www.sqlservercentral.com/Forums/Topic434103-149-1.aspx

This would probably be a good place to start. Perhaps you could alter these and then schedule them through SSRS.

share|improve this answer
2  
For your answer to be valuable over time, please include at least the main points from the referenced site. –  trygvis Dec 9 '12 at 23:21

There's actually a really easy way to do this without installing SSRS or any other tools. I used SQL Profiler, ran the reports stated, and got the code which I copied below. You will want this copied in a scheduled job and have it send out the mail from within the job and you're done!

This is the query passed for the 'jobs history' report:

exec sp_executesql @stmt=N'begin try
use msdb; 
Select (dense_rank() over(order by s1.job_id))%2 as l1
,       (dense_rank() over(order by s1.job_id,s2.step_id))%2 as l2
,       s1.job_id as [Job Id]
,       s1.name as [Job Name]
,       s2.step_id as [Step Id]
,       s2.step_name as [Step Name]
,       case when ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id  and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) is null 
                 then 0.0 
                 else ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id  and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) 
        end as [Total Executions]
,       Case when ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id  and s2.step_id = s3.step_id  and s3.run_status = 0  and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) is null
                then 0.0
                else ( Select count(*) from sysjobhistory s3 where ( s2.job_id = s3.job_id  and s2.step_id = s3.step_id  and s3.run_status = 0  and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) 
        end as [Failed Executions]
,       case when ( Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) is NULL 
                then 0.0 
                else ( Select avg((((run_duration)/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) 
        end as [Average Run Duration]
,       case when (Select avg(retries_attempted+0.0) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id  and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) is null 
                then 0.0 
                else (Select avg(retries_attempted+0.0) from sysjobhistory s3 where ( s2.job_id = s3.job_id and s2.step_id = s3.step_id and run_status <> 2 and datediff( day, convert(datetime, convert(varchar,s3.run_date)), getdate() ) < 7  ) ) 
        end  as [Average Retries Attempted] 
from sysjobs s1 
inner join sysjobsteps s2 on ( s1.job_id = s2.job_id ) 
order by s1.job_id, s2.step_id
end try
begin catch
select -100 as l1
,       1 as l2,        1 as [Job Id],  1 as [Job Name],1 as [Step Id],1 as [Step Name]
,       ERROR_NUMBER() as [Total Executions]
,       ERROR_SEVERITY() as [Failed Executions]
,       ERROR_STATE() as [Average Run Duration]
,       ERROR_MESSAGE() as [Average Retries Attempted]
end catch',@params=N''

This is the query for 'top jobs: 20 most frequently failing jobs'

exec sp_executesql @stmt=N'begin try
use msdb;
Select top  20 sj.job_id as [Job ID]
,       sj.name as [Job Name]
,       sl.name as [Owner Name]
,       jh.run_status
,       (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
,       (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
,       (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7  ) as [Execution Count]
,       (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average Run Duration]
,       (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average  Retries Attempted]
,       Count(*) as [Failure Count]
,    1 as l1 
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7 and jh.run_status = 0 
group by sj.job_id, sj.name, sl.name, jh.run_status  
order by [Failure Count] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
,       ERROR_NUMBER() as  [Execution Count]
,       ERROR_SEVERITY() as [Average Run Duration]
,       ERROR_STATE() as [Average  Retries Attempted]
,       ERROR_MESSAGE() as [Failure Count]
,       -100 as l1 
end catch',@params=N''

This is the query for '20 most frequently ran jobs':

exec sp_executesql @stmt=N'begin try
use msdb;
Select top 20 sj.job_id as [Job ID]
,       sj.name as [Job Name]
,       sl.name as [Owner Name]
,       (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
,       (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
,       (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7  ) as [Failure Count]
,       (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average Run Duration]
,       (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average  Retries Attempted]
,       Count(*) as [Execution Count]
, 1 as l1
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7 
group by sj.job_id, sj.name, sl.name
order by [Execution Count] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
,       ERROR_NUMBER() as  [Execution Count]
,       ERROR_SEVERITY() as [Average Run Duration]
,       ERROR_STATE() as [Average  Retries Attempted]
,       ERROR_MESSAGE() as [Failure Count]
,       -100 as l1 
end catch',@params=N''

20 slowest jobs:

exec sp_executesql @stmt=N'begin try
use msdb;
Select top 20 sj.job_id as [Job ID]
,       sj.name as [Job Name]
,       sl.name as [Owner Name]
,       (Select count(*) from sysjobschedules js1 where js1.job_id = sj.job_id ) as [Schedules Count]
,       (Select count(*) from sysjobsteps js2 where js2.job_id = sj.job_id ) as [Steps Count]
,       (Select count(*) from sysjobhistory jh1 where jh1.job_id=sj.job_id and jh1.step_id = 0 and jh1.run_status = 0 and datediff( day, convert(datetime, convert( varchar, jh1.run_date) ), getdate()) < 7  ) as [Failure Count]
,       (Select avg(((run_duration/10000*3600) + ((run_duration%10000)/100*60) + (run_duration%100))+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average Run Duration]
,       (Select avg(retries_attempted+0.0) from sysjobhistory jh2 where jh2.job_id=sj.job_id and jh2.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh2.run_date) ), getdate()) < 7  ) as [Average  Retries Attempted]
,       Count(*) as [Execution Count]
,1 as l1
from sysjobhistory jh
inner join sysjobs sj on ( jh.job_id = sj.job_id )
inner join sys.syslogins sl on ( sl.sid = sj.owner_sid )
where jh.step_id = 0 and datediff( day, convert(datetime, convert( varchar, jh.run_date) ), getdate()) < 7 
group by sj.job_id, sj.name, sl.name
order by [Average Run Duration] desc
end try
begin catch
select 1 as [Job ID],1 as [Job Name],1 as [Owner Name],1 as run_status,1 as [Schedules Count],1 as [Steps Count]
,       ERROR_NUMBER() as  [Execution Count]
,       ERROR_SEVERITY() as [Average Run Duration]
,       ERROR_STATE() as [Average  Retries Attempted]
,       ERROR_MESSAGE() as [Failure Count]
,       -100 as l1 
end catch',@params=N''
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.