How to Create Alert to Monitor Backup Size Database MS SQL Server in Solarwind DPA ?
How to Create Alert to Monitor Backup Size Database MS SQL Server in Solarwind DPA ?
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];
You will need to do a couple things to make this work:
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:
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.