Recover Log Shipping Secondary DB from status Suspect

Sometimes your Log Shipping Secondary database will be go into "Suspect" mode, due to various reasons including log shipping Restore Job failed, log backup file corrupted, and transaction log backup files grown too large due to maintenance task on primary database such as index rebuild. You can recover the secondary database by restoring a recently Full/Differential database Backup WITH NORECOVERY option. Below are the steps:

1. Make a Full/Differential database Backup from the Primary database;
2. Copy this database backup to the Secondary server;
3. Restore the Secondary database by this backup WITH NORECOVERY;
4. Start the Log Shipping Restore Job on Secondary server;
5. Check the Job History of the Log Shipping Restore Job, you can see it skips all the log backup files with LSN before the database backup you just restored;
   
6. If the Log Shipping Copy Job is still running, eventually the Restore Job will find the log backup files with LSN after the database backup;
   
7. At last the secondary database catch up and resumes.

Checking Log Shipping Performance

Below SQL script can be used to check the performance of Log Shipping, including the log backup size and time of delay between the primary database and secondary database.

DECLARE @dbname sysname, @days int
SET @dbname = 'SalonWebDB'
SET @days = -14 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
  WHEN rsh.restore_type = 'F' THEN 'File'
  WHEN rsh.restore_type = 'G' THEN 'Filegroup'
  WHEN rsh.restore_type = 'I' THEN 'Differential'
  WHEN rsh.restore_type = 'L' THEN 'Log'
  WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
  WHEN rsh.restore_type = 'R' THEN 'Revert'
  ELSE rsh.restore_type
 END AS [Restore Type],
 rsh.restore_date AS [Restore Date],
 bmf.physical_device_name AS [Restored From],
 bs.backup_start_date,
 bs.backup_finish_date,
 CAST(bs.backup_size / 1024.0 / 1024.0 AS decimal(19, 2)) AS [Backup Size MB],
 DATEDIFF(second, bs.backup_finish_date, rsh.restore_date) AS [Delay in sec.]
FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
AND rsh.restore_type = 'L'    -- Log
ORDER BY rsh.restore_history_id

Sample result:

Using the query result, you can plot a graph to see the trends of size and delay.