SQL Warm Standby Pulse, synced via LogShipping

--// 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

Comments :

0 comments to “SQL Warm Standby Pulse, synced via LogShipping”