At work our first line of defense for our SQL Servers is our system admins and engineers. Overtime I have written a decent number of scripts to try and help out our operations people so that when an alert comes in they can diagnose issues quickly and accurately. The following script checks and reports on the configuration of the log shipping primary server. It also does a simple analysis showing the most immedaite issue.
--The Job Status shows if the backup job has failed in the last 2 hours. select ISNULL(db.name,'') as [Database Name], CASE WHEN db.name IS NULL THEN 'DB is missing.' WHEN db.state_desc <> 'ONLINE' THEN 'DB is not online.' WHEN db.recovery_model_desc = 'SIMPLE' THEN 'DB is using simple recovery model.' WHEN pri.primary_database IS NULL THEN 'Missing log shipping primary config.' WHEN sec.secondary_database IS NULL THEN 'Missing log shipping primary secondary config.' WHEN job.name IS NULL THEN 'Backup job is missing.' WHEN job.enabled = 0 THEN 'Backup job is disabled.' WHEN schedule.name IS NULL THEN 'Job is missing a schedule.' WHEN schedule.enabled = 0 THEN 'Job schedule is disabled.' WHEN jobhist.run_status = 0 THEN 'Job has failed in the last 2 hours.' ELSE '' END AS [Immediate Issue], ISNULL(db.state_desc,'') as [Database State], ISNULL(db.recovery_model_desc,'') as [Recovery Model], ISNULL(pri.primary_database,'') as [Primary DB], ISNULL(sec.secondary_database,'') as [Secondary DB], ISNULL(sec.secondary_server,'') as [Secondary Server], ISNULL(job.name,'') as [Backup Job Name], CASE WHEN job.enabled = 0 THEN 'No' WHEN job.enabled = 1 THEN 'Yes' WHEN job.enabled IS NULL THEN '' END as [Job Enabled], CASE WHEN schedule.enabled = 0 THEN 'No' WHEN schedule.enabled = 1 THEN 'Yes' WHEN schedule.enabled IS NULL THEN '' ELSE 'Unknown' END AS [Schedule Enabled], CASE WHEN jobhist.run_status = 0 THEN 'Failure' WHEN jobhist.run_status = 1 THEN 'Successful' WHEN schedule.enabled=0 OR job.enabled=0 THEN 'Disabled' WHEN jobhist.run_status IS NULL THEN '' ELSE 'Unknown' END AS [Job Status] from sys.databases as db full outer join msdb.dbo.log_shipping_primary_databases as pri on db.name=pri.primary_database full outer join msdb.dbo.log_shipping_primary_secondaries as sec on db.name=sec.secondary_database OR pri.primary_id=sec.primary_id left join msdb.dbo.sysjobs as job on pri.backup_job_id=job.job_id left join msdb.dbo.sysjobschedules as jobschedule on job.job_id=jobschedule.job_id left join msdb.dbo.sysschedules as schedule on jobschedule.schedule_id=schedule.schedule_id left join (select job_id, min(run_status) AS [run_status] --0 is failed 1 is successful from msdb.dbo.sysjobhistory WHERE step_name='(Job outcome)' AND DATEDIFF(hh,convert(datetime, stuff(stuff(cast(run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + stuff(stuff(substring(cast(1000000 + run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120),GETDATE())<=2 --looks at the job history for the last 2 hours GROUP BY job_id) AS jobhist ON job.job_id=jobhist.job_id WHERE pri.primary_database is NOT NULL OR sec.secondary_database is NOT NULL ORDER BY db.name ASC