October 21, 2003 at 9:39 am
I have about 100 hundred jobs spread out through about 10 servers. I would like a report of the success/failure of this on a daily basis. Any good practices/techniques? I am assuming some mail notifications would be an idea for immediate notification. Any overhead with that? Thanks
October 21, 2003 at 10:01 am
October 22, 2003 at 5:36 am
try this:
exec master.dbo.xp_sendmail 'your email address',
@subject='server name Job Failures',
@query='
select b.server, a.name, b.step_name, b.sql_message_id, b.sql_severity,
b.message
from msdb.dbo.sysjobs a, msdb.dbo.sysjobhistory b
where a.job_id = b.job_id
and (b.run_date = convert(int,convert(varchar,getdate(),112)) or
b.run_date + 1 = convert(int,convert(varchar,getdate(),112)))
and b.run_status in (0,3)'
We schedule the above query on each of our servers to run daily around 7:00am. This show any jobs that had failed the previous day.
October 22, 2003 at 7:44 am
quote:
I have about 100 hundred jobs spread out through about 10 servers. I would like a report of the success/failure of this on a daily basis. Any good practices/techniques? I am assuming some mail notifications would be an idea for immediate notification. Any overhead with that? Thanks
I had the same issue at my company with servers all over the South East region of US. I found a script at this web site that I modified for my purpose. The author definitely deserves credit for his work!! Anyway, it creates a web page of jobs that failed and reason for failure. In another job, I also modified the code to create a listing of all enabled jobs and the last run date. We lost about a months worth of backups due to job not executing and not reporting a failure. With this we can see all jobs last run date. Create these DTS jobs on a single server. Modify them to email you on completion. Also you need to secure the .INI file for your access only. It contains SA passwords to your sites.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim xmlServers
Function Main()
' Read in the server list
InitServers
' Get the job status and write the web page
GetJobStatus
Main = DTSTaskExecResult_Success
End Function
Sub GetJobStatus()
Dim xmlServer
Dim ServerName
Dim ConnectionString
Dim cn
Dim rs
Dim SQL
Dim JobName
Dim ErrorMessage
Dim RunDate
Dim RunTime
Dim OK
Dim fso
Dim ts
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("\\<servername>\ISCommon\SQLWebLinks\Utilities\SQLJobStatus.htm", 2,-1)
Set cn = CreateObject("ADODB.Connection")
' If this is monday, the include the whole weekend
RunDate = Now()
if DatePart( "w", RunDate ) = 2 then
RunDate = DateAdd("d",-3,RunDate)
else
RunDate = DateAdd("d",-1,RunDate)
end if
ts.WriteLine "<HTML><HEAD>" & _
"<title>Gold Kist - SQL Server Job Status</title>" & _
"</HEAD>"
ts.WriteLine "<BODY>" & _
"<H3>SQL Server Job Failures</H3>" & _
"<H3>" & FormatDateTime(Now(),vbShortDate) & "</H3>" & _
"<H3>from " & FormatDateTime(RunDate,vbShortDate) & " to date</H3>" & _
"<TABLE border=1><TR><TH>Server</TH><TH>Job Name</TH><TH>Message</TH><TH>Failure Date</TH></TR>"
RunDate = Year(RunDate) * 10000 + Month(RunDate) * 100 + Day(RunDate)
SQL = "select j.name, jh.message, jh.run_date, jh.run_time" & _
" from sysjobs j" & _
" inner join sysjobhistory jh" & _
" on j.job_id = jh.job_id" & _
" and jh.step_id = 0" & _
" and run_status = 0" & _
" and run_date >= " & RunDate & _
" where j.enabled = 1" & _
" order by j.name, jh.run_date, jh.run_time "
For Each xmlServer In xmlServers.selectNodes("//Servers/Server")
ServerName = xmlServer.getAttribute("Name")
ConnectionString = "provider=sqloledb;server=" & ServerName & ";database=msdb;"
If xmlServer.getAttribute("IntegratedSecurity") = "Yes" Then
ConnectionString = ConnectionString & "Trusted_Connection=yes"
Else
ConnectionString = ConnectionString & "UID=" & _
xmlServer.getAttribute("UID") & ";PWD=" & _
xmlServer.getAttribute("PWD")
End If
ts.WriteLine "<TR><TD>" & ServerName & "</TD>"
On Error Resume Next
cn.Open ConnectionString
If Err.Number = 0 Then
On Error GoTo 0
Set rs = cn.Execute(SQL)
Do Until rs.EOF
If Not IsNull(rs("message").Value) Then
JobName = rs("name").Value
ErrorMessage = rs("message").Value
RunDate = DateSerial(rs("run_date").Value / 10000, rs("run_date").Value / 100 Mod 100, rs("run_date").Value Mod 100)
RunTime = TimeSerial(rs("run_time").Value / 10000, rs("run_time").Value / 100 Mod 100, rs("run_time").Value Mod 100)
ts.WriteLine "<TR><TD>" & ServerName & "</TD><TD>" & JobName & _
"</TD><TD>" & ErrorMessage & _
"</TD><TD>" & FormatDateTime(RunDate,vbshortdate) & _
" " & FormatDateTime(RunTime, vbShortTime) & "</TD>"
End If
ts.WriteLine "</TR>"
rs.MoveNext
Loop
rs.Close
cn.Close
Else
ts.WriteLine "<TD>Unable to connect:" & Err.Description & "</TD></TR>"
End If
On Error GoTo 0
Next
ts.WriteLine "</TABLE></BODY></HTML>"
ts.Close
Set ts = Nothing
Set fso = Nothing
End Sub
Sub InitServers()
set xmlServers = createObject("MSXML.DOMDocument")
'xmlServers.Load "\\<servername>\ISCommon\SQLWebLinks\Utilities\SQLServers.ini"
xmlServers.Load "\\<servername>\SQLUtilities\SQLServers.ini"
End Sub
Your INI format (add yours servers with the following format and save as "SQLServers". Remember to secure.)
<Servers>
<Server Name="<servername>" UID="sa" PWD="<your_password>" />
</Servers>
Here's the code for the second job:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim xmlServers
Function Main()
' Read in the server list
InitServers
' Get the last run date and write the web page
GetRunStatus
Main = DTSTaskExecResult_Success
End Function
Sub GetRunStatus()
Dim xmlServer
Dim ServerName
Dim ConnectionString
Dim cn
Dim rs
Dim SQL
Dim JobName
Dim ErrorMessage
Dim RunDate
Dim RunTime
Dim OK
Dim fso
Dim ts
Dim strYellow
Dim strRed
Dim RunDateMinusOne
strYellow = "#FFFF00"
strRed = "#FF0000"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("\\<servername>\ISCommon\SQLWebLinks\Utilities\SQLJobsLastRunDate.htm", 2,-1)
Set cn = CreateObject("ADODB.Connection")
ts.WriteLine "<HTML><HEAD>" & _
"<title>Gold Kist - SQL Server Utilities - - Enabled Jobs Last Run Date</title>" & _
"</HEAD>"
ts.WriteLine "<BODY>" & _
"<H3>SQL Server Enabled Jobs LAST RUN DATE... " & FormatDateTime(Now(),vbShortDate) & "</H3>" & _
"<H3>Check jobs that are colored... White is OK." & "</H3>" & _
"<TABLE border=1><TR><TH>Server</TH><TH>Job Name</TH><TH>Last Run Date</TH></TR>"
RunDateMinusOne = Year(date()) * 10000 + Month(date()) * 100 + Day(date()) - 1
SQL = "select originating_server, name, MAX(run_date) AS run_date FROM sysjobs " & _
"left join sysjobhistory " & _
"on sysjobs.job_id = sysjobhistory.job_id " & _
"and step_id = 0 " & _
"where enabled = 1 " & _
"group by originating_server, name"
For Each xmlServer In xmlServers.selectNodes("//Servers/Server")
ServerName = xmlServer.getAttribute("Name")
ConnectionString = "provider=sqloledb;server=" & ServerName & ";database=msdb;"
If xmlServer.getAttribute("IntegratedSecurity") = "Yes" Then
ConnectionString = ConnectionString & "Trusted_Connection=yes"
Else
ConnectionString = ConnectionString & "UID=" & _
xmlServer.getAttribute("UID") & ";PWD=" & _
xmlServer.getAttribute("PWD")
End If
ts.WriteLine "<TR><TD>" & ServerName & "</TD>"
On Error Resume Next
cn.Open ConnectionString
If Err.Number = 0 Then
On Error GoTo 0
Set rs = cn.Execute(SQL)
Do Until rs.EOF
If IsNull(rs("run_date").Value) Then
RunDate = " "
JobName = rs("name").Value
ts.WriteLine "<TR><TD BGCOLOR=" & strRed & ">" & ServerName & "</TD><TD BGCOLOR=" & strRed & ">" & JobName & _
"</TD><TD BGCOLOR=" & strRed & ">" & RunDate & "</TD>"
Else
RunDate = DateSerial(rs("run_date").Value / 10000, rs("run_date").Value / 100 Mod 100, rs("run_date").Value Mod 100)
JobName = rs("name").Value
If rs("run_date") < RunDateMinusOne Then
ts.WriteLine "<TR><TD BGCOLOR=" & strYellow & ">" & ServerName & "</TD><TD BGCOLOR=" & strYellow & ">" & JobName & _
"</TD><TD BGCOLOR=" & strYellow & ">" & FormatDateTime(RunDate,vbshortdate) & "</TD>"
Else
ts.WriteLine "<TR><TD>" & ServerName & "</TD><TD>" & JobName & _
"</TD><TD>" & FormatDateTime(RunDate,vbshortdate) & "</TD>"
End If
End If
ts.WriteLine "</TR>"
rs.MoveNext
Loop
rs.Close
cn.Close
Else
ts.WriteLine "<TD>Unable to connect:" & Err.Description & "</TD></TR>"
End If
On Error GoTo 0
Next
ts.WriteLine "</TABLE></BODY></HTML>"
ts.Close
Set ts = Nothing
Set fso = Nothing
End Sub
Sub InitServers()
set xmlServers = createObject("MSXML.DOMDocument")
'xmlServers.Load "\\<servername>\ISCommon\SQLWebLinks\Utilities\SQLServers.ini"
xmlServers.Load "\\<servername>\SQLUtilities\SQLServers.ini"
End Sub
Replace <servername> with server that will host your webpage and contain your INI file. Hope you find this helpful.
Curtis
October 22, 2003 at 8:55 am
Here is what I do:
I have all the SQL servers of interest linked to my desktop SQL server. I have a single table and a stored procedure on the same PC.
The stored procedure loops trough the master.sysservers table and for each server listed there it executes a distributed query of of the form 'Insert into ServerJobs Select LinkedServer.msdb.dbo.sysjobservers ... inner join LinkedServer.msdb.dbo.sysjobs....'
and this way the local table will have the server name, job name, description, run time, duration, outcome for all jobs on all servers. After the table has been populated a simple report is created and e-mailed to myself using xp_sendmail. This could be useful if there is no e-mail client installed on the SQL server boxes.
regards
Stanislav Petkov
Stanislav Petkov
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply