***************************************************Introduction********************************************************
Today i will Dessecus how to Get all Database job history then Send Email to The Technical Support “job History Report” then i will delete this log history
Get Daatabase job history
select * from msdb.dbo.sysjobs
Get database job Step history
select * from msdb.dbo.sysjobhistory
Now i will make join between msdb.dbo.sysjobhistory and msdb.dbo.sysjobs by Job_ID and i will Convert the job tun Date time because if you select it without convert you will sedd the Data Formate “20121224” so i will convert it , also i have column name “Run_Status” this Column return 0 or 1 { 0 = the job is Failed , 1 = The job is Success } so i will make Case when on this Column this Script you can use it for to ways
1- job Failed history report
2- job Success history Report
but i will write now ho to get job all history Failed and Success
select J.job_id,J.nameas[Job Name],S.step_name,S.step_id,
CONVET (DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time,
RIGHT(‘000000’+CONVERT(varchar(6),S.run_duration), 6)as[Run Duration],
CASE S.run_status
WHEN 0 THEN‘JOB FAIL’
WHEN 1 THEN‘JOB Success’
END
,S.message,S.server, CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time
from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J
on s.job_id=J.job_id
where Step_id<> 0
/*
************************************ Send Email With Database job History Report**************************************
If you need to know hot Configure your database mail See this post in the First http://wp.me/p1Oidq-9A
*/
DECLARE @xmlNVARCHAR(MAX)
DECLARE @bodyNVARCHAR(MAX)
SET @xml=CAST((SELECTJ.nameAS‘td’,”,S.step_nameAS‘td’,”,S.step_idAS‘td’,”,CASES.run_status
WHEN 0 THEN‘JOB FAIL’
WHEN 1 THEN‘JOB Success’
END AS‘td’,
”,CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 AS‘td’,”,S.messageAS‘td’
from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J
on s.job_id=J.job_id
where Step_id<> 0
FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))
SET @body=‘<html><body><H3>Database job history</H3>
<table border = 1>
<tr><font color=blue size=5 >
<td> Job Name </td> <td> Step Name </td> <td> Step ID </td> <td> Job Status </td> <td> Job_Run_Time </td> <td> message </td></font></tr>’
SET @body=@body+@xml+‘</table></body></html>’
EXEC msdb.dbo.sp_send_dbmail
@profile_name =‘DBA’,— replace with your SQL Database Mail Profile
@body =@body,
@body_format =‘HTML’,
@recipients =‘Mostafa@Safeerp.com’,— replace with your email address
@subject =‘Database job History’;
/*
************************************ Cleare job history**************************************************
If you need to know hot Configure your database mail See this post in the First
*/
EXEC MSDB.dbo.sp_purge_jobhistory
@job_name=N’testsenemail’; — JOB Nmar
GO
Like this:
Like Loading...
DB Cloud TECH 