Jeremy Carter

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