New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


How can I monitor schedule jobs of MS SQL server by PRTG?

Votes:

0

Your Vote:

Up

Down

Is it possible to monitor schedule jobs of MS SQL server by PRTG network monitor? If yes how?

prtg schedule-monitor sql

Created on Mar 30, 2010 12:43:47 PM by  idownload2009 (0) 1

Last change on Apr 1, 2010 2:07:00 PM by  Daniel Zobel [Paessler Support]



3 Replies

Accepted Answer

Votes:

0

Your Vote:

Up

Down

Query SQL's System Database

If you are using SQL 2005, the job rersults are stored in msdb.dbo.sysmaintplan_log and the actual job names in msdb.dbo.sysmaintplan_plans

With this information we can create a Stored Procedure like:

CREATE PROCEDURE spPlanResult
@nvcPlanName   nvarchar(255)

AS
BEGIN
	SELECT TOP 1 succeeded  
	FROM msdb.dbo.sysmaintplan_log PL
	INNER JOIN msdb.dbo.sysmaintplan_plans PP ON PP.id = PL.plan_id
	WHERE PP.name = @nvcPlanName
	ORDER BY start_time desc
END

Parsing the job name to the Stored Procedure will return 1 if the last run of the job has succeeded and 0 if not.

We can now add a Microsoft-SQL sensor that runs the Stored Procedure (SQL expression = exec spPlanResult 'JobName') and check for the return value.

Created on Mar 31, 2010 11:34:48 AM by  PRTG Tools Family [prtgtoolsfamily.com] (12,865) 3 4



Votes:

0

Your Vote:

Up

Down

Hello I have tried this script in the query analyzer and it works fine, it accually returns ’Succeeded’ instead of 1 but that should not be a problem.

My problem is when I try to set it up in the PRTG Network Monitor I get the error “Empty Resultset”, I have traced the query in the SQL Profiler and it runs perfectly on the server. I have tried a couple of different configurations but I cannot get it to work. Any suggestions what I have done wrong? /John

Created on Apr 15, 2010 2:48:13 PM by  John Jacobson (0)



Votes:

0

Your Vote:

Up

Down

Perhaps you don't have any jobs set up currently and it's returning NULL?

I set up a check to look for any failures in all Enabled jobs. Here's the code:

SELECT MIN(h.run_status) FROM sysjobhistory h INNER JOIN sysjobs j ON h.job_id = j.job_id WHERE j.enabled = 1 AND h.instance_id IN (SELECT MAX(h.instance_id) FROM sysjobhistory h GROUP BY (h.job_id))

Created on Oct 27, 2010 4:19:20 PM by  echoWhiskey (0)



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.