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.