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


Does PRTG can support Database backup jobs monitoring?

Votes:

1

Your Vote:

Up

Down

Hi,

I successfully setup basic SQL performance using the SQL server sensors available in PRTG. But I have problem in setting up special DB monitors below? Please advise if PRTG can support this?

Databases - Days Since Last Backup
Databases - Unavailable
Databases - Jobs Failed
SQL - Long Running SQL
Clusters - Node Unavailable
Network - SQL Server Packets Error Rate
Monitored Server - Connection Failure

Thanks!

backups database monitoring

Created on May 6, 2011 12:36:14 AM by  TrendGISS (1) 1

Last change on May 6, 2011 12:59:35 PM by  Torsten Lindner [Paessler Support]



Best Answer

Accepted Answer

Votes:

1

Your Vote:

Up

Down

For the number of days since the last backup of a single database:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackup]
	@DatabaseName	nvarchar(50)
AS
BEGIN
    SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    WHERE s.Name = @DatabaseName
END

Step 2

Add a new MSQL sensor

On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter

exec dbo.spLastBackup YOUR_DATABASE_NAME

In the channels tab select the value channel and enter Day as unit.

For the number of days since the last backup of all your databases:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
    SELECT  
        s.name AS Channel
	,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
	,1 as IsInt
	,'Days' as Unit
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    GROUP BY s.name
END

Step 2

Add a new Custom XML sensor

On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml

Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server

Created on May 7, 2011 2:51:21 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,745) 3 4

Last change on Oct 20, 2014 11:00:41 AM by  Torsten Lindner [Paessler Support]



13 Replies

Votes:

0

Your Vote:

Up

Down

Hello,

I'm afraid we don't have specific sensors for those. You could only try using the "normal SQL"-sensors, if you can check these items with SQL Queries.

best regards.

Created on May 6, 2011 12:59:20 PM by  Torsten Lindner [Paessler Support]



Accepted Answer

Votes:

1

Your Vote:

Up

Down

For the number of days since the last backup of a single database:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackup]
	@DatabaseName	nvarchar(50)
AS
BEGIN
    SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    WHERE s.Name = @DatabaseName
END

Step 2

Add a new MSQL sensor

On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter

exec dbo.spLastBackup YOUR_DATABASE_NAME

In the channels tab select the value channel and enter Day as unit.

For the number of days since the last backup of all your databases:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
    SELECT  
        s.name AS Channel
	,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
	,1 as IsInt
	,'Days' as Unit
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    GROUP BY s.name
END

Step 2

Add a new Custom XML sensor

On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml

Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server

Created on May 7, 2011 2:51:21 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,745) 3 4

Last change on Oct 20, 2014 11:00:41 AM by  Torsten Lindner [Paessler Support]



Votes:

1

Your Vote:

Up

Down

TYPO

It's GROUP BY at the end and not "GROUP BT". For all those who have no knowledge about SQL Query's and the Code didn't worked ;)

Created on Oct 20, 2014 9:27:08 AM by  Thomas Umbricht (2,820) 2 3



Votes:

0

Your Vote:

Up

Down

Thanks for the note. I did change it.

Created on Oct 20, 2014 9:30:05 AM by  Torsten Lindner [Paessler Support]



Votes:

0

Your Vote:

Up

Down

When I filled the required parameters, need I put entire -d -sp -s -u -p ?

Because I would monitor all databases on my MS SQL Server and I would know if is -d necessary ? And I would know if -u and -p too ?

Thank you in advance

Created on Sep 27, 2016 2:10:52 PM by  btridon (0) 1



Votes:

0

Your Vote:

Up

Down

Hello,

this sensors is not supported by the Paessler technical support, please contact teh vendor to the sensor directly (support@prtgtoolsfamily.com).

Created on Sep 28, 2016 3:31:58 PM by  Jochen Greger [Paessler Support]



Votes:

6

Your Vote:

Up

Down

Created a SQL script that actually simply reports the amount of databases backed up with a parameter (number - hours since last backup), how many databases haven't been backed up and how many databases exist on the system.

Simply create the .SQL file for PRTG sensor and execute on the MASTER database.

Set the parameter value in the sensor to e.g. 26 hours (every night backups, plus 2 hours to finish).

Works pretty well with several SQL servers and gives us the necessary information. On top of that we have some file-sensor on the most important DBs running.

The amount of backed up databases vs. all databases vs. not recently backed up depends all on your SQL configuration. We use e.g. LiteSpeed as well and this still works pretty good. Just find out how many databases should be backed up and fix the number in the sensor with minimum and maximum limits so alerts get send out (upper and lower limit should be the same and mirror the amount of DBs expected to be backed up).

Set "RecentlyBackupUpCount" as default channel for better visibility.

Hope this example script helps some of you! I left all my notes in the script as well...

USE master
DECLARE @MaxHours int
SET @MaxHours = @prtg

SELECT 
-- how many databases do we have on this server?
(
                SELECT COUNT(Name) FROM sys.sysdatabases WHERE name NOT LIKE 'tempdb'
) AS TotalAmountOfDatabases,

(
                -- how many databases where backed up within the last n hours?
                SELECT COUNT(BackedUpDBs.DatabaseName) FROM  (
                                SELECT sdb.Name AS DatabaseName
                                ,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
                                ,MAX(bus.backup_finish_date) AS LastBackupDateTime
                                ,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

                                FROM sys.sysdatabases sdb
                                                LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

                                WHERE 1=1 -- 1=1 as dummy placeholder
                                AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
                                AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
                                AND bus.is_damaged = 0
                                --AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx 
                                --no checking recovery model, this can be set in the settings per DB
                                AND bus.backup_finish_date IS NOT NULL -- since this can be null, we only check for those which aren't

                                GROUP BY sdb.Name 
                ) AS BackedUpDBs 
                WHERE HoursAgo <= @MaxHours
) AS RecentlyBackupUpCount,

(
                -- how many databases where NOT backed up within the last n hours?
                SELECT COUNT(NotBackedUpDBs.DatabaseName) FROM  (
                --SELECT NotBackedUpDBs.DatabaseName, * FROM  (
                                SELECT sdb.Name AS DatabaseName
                                ,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
                                ,MAX(bus.backup_finish_date) AS LastBackupDateTime
                                ,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

                                FROM sys.sysdatabases sdb
                                LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

                                WHERE 1=1 -- 1=1 as dummy placeholder
                                AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
                                AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
                                --AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx 
                                --no checking recovery model, this can be set in the settings per DB

                                GROUP BY sdb.Name 
                ) AS NotBackedUpDBs
                WHERE HoursAgo > @MaxHours
) AS NOTRecentlyBackupUpCount

Regards Florian Rossmark

Created on Mar 30, 2017 11:50:57 PM by  Florian Rossmark (4,018) 3 2



Votes:

0

Your Vote:

Up

Down

And what about a PowerShell script ?

https://blogs.technet.microsoft.com/heyscriptingguy/2011/05/02/use-powershell-to-report-sql-server-backup-status/

it looks pretty easy: dir SQLSERVER:\SQL\localhost\DEFAULT\Databases | Select Name, LastBackupDate

Or with Event View =>entries 12288, 12289, 18264, 208

Seems really simple

Created on Mar 9, 2018 12:28:57 PM by  Yann (150) 1 1



Votes:

0

Your Vote:

Up

Down

Hello

the correct log entries are:

Event ID Symbolic Name Text Notes 12288 DTS_MSG_PACKAGESTART Package "" started. The package has started to run. 12289 DTS_MSG_PACKAGESUCCESS Package "" finished successfully. The package successfully ran and is no longer running. 12290 DTS_MSG_PACKAGECANCEL Package "%1!s!" has been cancelled. The package is no longer running because the package was canceled. 12291 DTS_MSG_PACKAGEFAILURE Package "" failed. The package could not run successfully and stopped running.

Top MS link => https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package

Simply check for an 12291 event.

Created on Mar 13, 2018 8:53:14 AM by  Yann (150) 1 1



Votes:

0

Your Vote:

Up

Down

Hey,

I've modified the script above to exclude system databases as per below:

SELECT s.name AS Channel ,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value ,1 as IsInt ,'Days' as Unit FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') GROUP BY s.name

Bit of a dirty way to exclude system DBs, but it works fine and parses all the channels into a single PRTG sensor. Great, nice and efficient so far.

However, my question is, how do I get the sensor to go into warning or alarm state when the 'Days' is greater than 1 (or any other number I might choose).

I can configure notifications for each channel individually, but this is impractical as I have anywhere up to 50 user databases I need to keep an eye on. It also doesn't show the sensor in warning or alarm state on the map/interface.

Any ideas?

Created on Jan 9, 2019 4:11:16 AM by  Cloud_0013 (60) 2 1



Votes:

1

Your Vote:

Up

Down

Hello Cloud_0013,

The way to go is to enable limits for each channel and set up a max warning or max error limit at 1 day. Next you will need to add a notification trigger to the sensor.

How to Set Channel Limits
PRTG Manual: Sensor Notification Triggers Settings

Setting multiple limits in one batch, the Channel Limits tool might be of help.


Sensors | Multi Channel Sensors | Tools | Notifications

Kind regards,

[[http://prtgtoolsfamily.com]] PRTGToolsFamily

Created on Jan 9, 2019 12:42:08 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,745) 3 4



Votes:

1

Your Vote:

Up

Down

With a little help from PRTGToolsFamily (thanks, you're a legend) I've modified the SQL stored proc as follows:

SELECT s.name AS Channel ,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value ,1 as IsInt ,'Days' as Unit ,1 as limitMode ,0 as limitMinWarning ,0 as limitMinError ,1 as limitMaxWarning ,2 as limitMaxError ,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg ,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') GROUP BY s.name

This enables error and warning alarm limits and messages by default on all channels that come through on the results.

The trick is the limit settings are only loaded the first time the sensor scans, so you'd have to delete and re-add the sensor if you we're altering an existing one.

Another tip is that you after you create the Stored Proc, you have to add the user to it (will be in the 'Master' Database if you use the script above) with EXECUTE permissions, even if it's the sa user.

Created on Jan 11, 2019 1:03:44 AM by  Cloud_0013 (60) 2 1

Last change on Jan 14, 2019 5:26:29 AM by  Sven Roggenhofer [Paessler Technical Support]



Votes:

1

Your Vote:

Up

Down

To add a few more tips:

  • If you do not want to use all limits (like the limitMinWarning and limitMinError) you can simply omit then, there is no need to set them to 0.
  • Altering the limit settings afterwards can also be done manual if you do not want to create the sensor anew, manual alterations will overrule the ones at creation time.
  • After sensor creation it is even possible to remove the limit settings from your SQL result set. This will reduce the overhead but will also require a heads-up when you recreate the sensor.

Sensors | Multi Channel Sensors | Tools | Notifications

Kind regards,

[[http://prtgtoolsfamily.com]] PRTGToolsFamily

Created on Jan 11, 2019 7:43:53 AM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,745) 3 4



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.