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!
Article Comments
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
May, 2011 - Permalink
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 ;)
Oct, 2014 - Permalink
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
Sep, 2016 - Permalink
Hello,
this sensors is not supported by the Paessler technical support, please contact teh vendor to the sensor directly (support@prtgtoolsfamily.com).
Sep, 2016 - Permalink
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
Mar, 2017 - Permalink
And what about a PowerShell script ?
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
Mar, 2018 - Permalink
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.
Mar, 2018 - Permalink
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?
Jan, 2019 - Permalink
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,
PRTGToolsFamily
Jan, 2019 - Permalink
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.
Jan, 2019 - Permalink
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,
PRTGToolsFamily
Jan, 2019 - Permalink
As an FYI I have made further refinements to the stored proc.
In my environment we occasional decommission databases as clients cancels services with us, in this scenario we take the database offline for a 90 day grace period before we delete and purge the data completely. These offline databases were triggering 'false errors' on this sensor, as our backup job is set to exclude offline databases.
The below refined SQL script will exclude offline databases from the results, removing false triggers for databases that are offline:
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', 'distribution', 'ReportServer', 'ReportServerTempDB')
AND status & 512 <> 512
GROUP BY s.name
The offline databases then display in the PRTG interface as greyed out with 'No Data', and will not trigger Warnings/Alarms. The only way I can figure out to remove them completely is to delete and recreate the sensor from scratch, which is potentially undesirable as you lose all the history.
Cheers!
Jun, 2019 - Permalink
Hello, I used the script above using hours instead of days, I see the correct results on PRTG interface but the limit seems not working for me.
For example now I see 19 on interface but there isn't any warning or errors.
SELECT
s.name AS Channel
,DATEDIFF(hour, MAX(b.backup_finish_date), getdate()) as Value
,1 as IsInt
,'Hours' as Unit
,1 as limitMode
,0 as limitMinWarning
,0 as limitMinError
,12 as limitMaxWarning
,18 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', 'distribution', 'ReportServer', 'ReportServerTempDB')
AND status & 512 <> 512
GROUP BY s.name
PRTG wrote : 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.
How do it ?
Thanks
Dec, 2020 - Permalink
Hello
The stored procedure i'm using works fine with SQL studio:
############################################################
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spLastBackups] Script Date: 15.09.2023 10:35:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
,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 ('model', 'tempdb', 'msdb', 'Resource')
GROUP BY s.name
END
###################################################################
I am trying to geet this working, but when i run (h****a\**_prtg is the service user for PRTG)
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML>SQLspXML.exe -s=s*****1.h***a.**** -d=master -sp=spLastBackups -u=h****a\**_prtg -p=*** 20.4.1.9 Checking .NET Framework version. Minmal required is... 4.7 Installed version is... 4.7.2 Ok Impersonating user h****a\*****_prtg
and it stays blocked here...
Any idea or suggestion?
Thanks a lot for your help =)
Sep, 2023 - Permalink
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.
May, 2011 - Permalink