What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

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

Votes:

0

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

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



3 Replies

Accepted Answer

Votes:

0

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



Votes:

0

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



Votes:

0

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




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.