--// SQL Warm Standby Pulse, synced via LogShipping --// TODO: Create job to monitor age and send email alerts --// ACKNOWLEDGED: yeah there's other built-in ways to accomplish this. DECLARE @THRESHOLD_HOURS INT SELECT @THRESHOLD_HOURS = 8 ; WITH CTE AS ( SELECT ROW_NUMBER() OVER (PARTITION BY rh.destination_database_name ORDER BY rh.restore_history_id DESC) Seq , rh.destination_database_name DBName , rh.restore_date LstRestoreDate , REVERSE(LEFT(REVERSE(physical_device_name), CHARINDEX('\', REVERSE(physical_device_name)) - 1)) LogFileName , bs.backup_start_date LogFileDateCreated , bs.last_lsn FROM msdb.dbo.restorehistory rh (NOLOCK) LEFT JOIN msdb.dbo.backupset bs (NOLOCK) ON bs.backup_set_id = rh.backup_set_id LEFT OUTER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id ) SELECT DBName , CASE WHEN LogFileDateCreated < DATEADD(hh, @THRESHOLD_HOURS * -1, GETDATE()) THEN '> ' + LTRIM(STR(@THRESHOLD_HOURS)) + ' HOUR !!' ELSE '' END [ACHTUNG] , DATEDIFF(hh, LogFileDateCreated, GETDATE()) [AGE(HOURS)] , LstRestoreDate , LogFileName , LogFileDateCreated , last_lsn FROM CTE WHERE Seq = 1 ORDER BY DBName
UKVMUG 2019 Summary and Links
1 day ago
Comments :
Post a Comment