Monitor SQL Server Agent Job: Udvid med run_duration
Hej,Vi bruger et vbscript til at tjekke status på kørte jobs på sqlserver hentet fra http://www.wisesoft.co.uk/scripts/vbscript_monitor_sql_server_agent_jobs.aspx :
ServerName = "servernavn"
SET cmd = CREATEOBJECT("ADODB.Command")
SET cn = CREATEOBJECT("ADODB.Connection")
SET rs = CREATEOBJECT("ADODB.Recordset")
cn.open "Provider=SQLOLEDB.1;Data Source=" & ServerName & ";Integrated Security=SSPI"
cmd.activeconnection =cn
cmd.commandtext = "select name, run_status, substring(cast(run_date as varchar(8)),7,2) + '/' + " & _
"substring(cast(run_date as varchar(8)),5,2) + '/' + " & _
"substring(cast(run_date as varchar(8)),1,4) + ' ' + case len(cast(run_time as varchar(6))) " & _
"when 6 then left(cast(run_time as varchar(6)),2) + ':' + substring(cast(run_time as varchar(6)),3,2) " & _
"when 5 then left(cast(run_time as varchar(6)),1) + ':' + substring(cast(run_time as varchar(6)),2,2) " & _
"else '00:00' end as 'RunTime' " & _
"from msdb.dbo.sysjobhistory sjh " & _
"join msdb.dbo.sysjobs_view sjv on sjh.job_id = sjv.job_id " & _
"where instance_id in " & _
"(select top 1 instance_id from msdb.dbo.sysjobhistory sjh2 where step_name = '(Job outcome)' and sjh.job_id " & _
"= sjh2.job_id " & _
"order by instance_id desc) " & _
"order by run_status, instance_id DESC"
SET rs =cmd.EXECUTE
message = ServerName & " SQL Agent Job History" & vbcrlf & vbcrlf & _
"Status" & vbtab & vbtab & "Execution Time" & vbtab & vbtab & "Job Name" & vbcrlf
WHILE rs.eof<>true AND rs.bof<>true
IF rs(1) = 0 THEN
message = message & "FAILED" & vbtab & vbtab & rs(2) & vbtab & vbtab & rs(0) & vbcrlf
ELSEIF rs(1) = 1 THEN
message = message & "SUCCEEDED" & vbtab & rs(2) & vbtab & vbtab & rs(0) & vbcrlf
ELSE
message = message & "UNKNOWN" & vbtab & rs(2) & vbtab & vbtab & rs(0) & vbcrlf
END IF
rs.movenext
WEND
MsgBox message ,, ServerName & " SQL Agent Job History"
--
Nogen der kan hjælpe med at få implementeret run_duration således at vi kan se hvor lang tid jobbet var om at køre?
