How to Create Alert MS SQL Server

Question

How to Create Alert to Monitor Backup Size Database MS SQL Server in Solarwind DPA ?

darichar · Answer

You will need to do a couple things to make this work:

* Create a Custom SQL Alert that uses multiple numeric resultsets, i.e. a name/value pair. It looks like you may have created a single numeric return alert.

* Customize the query to return a name/value pair, i.e. in this case the database name and the size in GB of the backup.

WITH LastBackUp AS (

SELECT bs.database_name,

bs.backup_size,

bs.backup_start_date,

bmf.physical_device_name,

Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )

FROM  msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

WHERE bs.[type] = 'D'

AND bs.is_copy_only = 0

AND Position = 1

)

SELECT database_name AS [Database],

backup_size/1024.0/1024.0 AS backup_size_gb

FROM LastBackUp

ORDER BY [Database];

My alert definition then looks like this:

irfan.hidayat · Answer

Thank you very much for your support, is the meaning of the "[ ]" sign replaced according to our database?

may I ask the FULL script to compare, because I still don't understand the script.

This is my setup :

Please review my setup

mandevil · Answer

Found this script on Stackexchange that might do what you need. You can modify it for your purposes - like do a sum(bs.backup_size) in the select and then use that numeric to compare against some size for when you would like to get notified. When you get something working per how you want to use it, do you mind posting back here? I'd really like to see what you used in your env. WITH LastBackUp AS
(
SELECT bs.database_name,
bs.backup_size,
bs.backup_start_date,
bmf.physical_device_name,
Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
AND bs.is_copy_only = 0
)
SELECT
database_name AS [Database],
CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
backup_start_date AS [Last Full DB Backup Date],
physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];