September 4, 2013 at 9:43 pm
Comments posted to this topic are about the item Monitoring job history using Reporting Services
Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/
September 5, 2013 at 7:22 am
Nice Article... reminds me of Idera's SQL Job manager (free).
http://www.idera.com/productssolutions/freetools/sqljobmanager
September 5, 2013 at 12:02 pm
THanks so much for this - I am going to add a few parameters (default is the way you ran it) but running it for a multi select of jobs or range of time/date would make this just wonderful
September 5, 2013 at 12:17 pm
Thank you - this just made my life a bit easier 😀
September 5, 2013 at 1:35 pm
The clause:
AND CAST(SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 5,2) +'-'
+ SUBSTRING(CONVERT(VARCHAR(10),h.run_date) , 7,2) +'-'
+ SUBSTRING(CONVERT(VARCHAR(10),h.run_date),1,4) AS SMALLDATETIME) = CONVERT(VARCHAR(10), GETDATE(), 121)
is destined to scan the entire sysjobhistory table.
For small sysjobhistory row counts, that scan may be acceptable. For large sysjobhistory row counts, the same scan can result in unacceptable blocking. To avoid the scan, it might be useful to create an index upon sysjobhistory(run_date). But, the index would need to be checked (verified to be in place and functional) after each msdb upgrade. To harness seeking, the above clause would also be rewritten, so that all scalar operations are solely accomplished upon GETDATE(), and never accomplished upon a column (such as h.run_date, as done in above clause), with the run_date clause instead being BETWEEN two such scalar operations.
For similar upgrade reasons (this time concerning upgrades of master, not msdb), a reliance upon master.dbo.spt_values might be risky. Its DDL and purpose are not documented, which means Microsoft can change its DDL or its contents (whenever Microsoft see fit, without any advanced public notice). It thus may be more robust to create a [Numbers] table and to reference it (instead of master.dbo.spt_values).
September 5, 2013 at 2:58 pm
I've been thinking of doing something similar for a while, so your post pushed me in the right direction.
I noticed you have a lot of code just to calculate the JobStart and JobEnd dates. You can shorten the JobStart by using a function from the msdn database:
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
I got this from the following link, there are some other techniques in there that might also help:
http://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data
for the JobEnd, I used the same function along with a (lengthy) calculation to convert the run_duration into seconds:
dateadd(
s,
((h.run_duration/1000000)*86400) + (((h.run_duration-((h.run_duration/1000000)*1000000))/10000)*3600) + (((h.run_duration-((h.run_duration/10000)*10000))/100)*60) + (h.run_duration-(h.run_duration/100)*100),
msdb.dbo.agent_datetime(run_date, run_time)
)
I got the duration calculation from another forum post:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply