MSDN:
"SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled."
The Environment is a Microsoft Azure Cloud 2-Node Windows Server 2016 Fail-Over Cluster running Microsoft SQL Server 2016 Standard with Always-ON Availability Groups and Transactional Replication, plus Log Shipping to a Disaster Recovery Site. Below it the T-SQL to Monitor the Log Shipping Components.
T-SQL - Monitor Log Shipping:
--Returns a result set containing status and other information for registered primary and secondary databases on a primary, secondary, or monitor server.
Exec sp_help_log_shipping_monitor
--Get Log Shipping SQL Jobs
SELECT * from msdb.dbo.sysjobs where category_id = 6
--Log Shipping: Get SQL Job History: Copy Job (include your JobID)
SELECT msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', * from msdb.dbo.sysjobhistory where job_id = 'D74C0F28-8626-4FD2-AA8F-72D00DCF5FDD'
--Uncomment to get only the Last Run History
and msdb.dbo.agent_datetime(run_date, run_time) = (Select top 1 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime' from msdb.dbo.sysjobhistory where job_id = 'D74C0F28-8626-4FD2-AA8F-72D00DCF5FDD' order by RunDateTime desc)
order by RunDateTime DESC, instance_id desc
--EXEC msdb.dbo.sp_help_jobhistory @job_name = N'LS_Copy_xyz' ;
--Get Last Run History: Restore Job (include your JobID)
SELECT msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', * from msdb.dbo.sysjobhistory where job_id = 'E19D351F-22B8-4624-A8A2-102C094D5E84'
--Uncomment to get only the Last Run History
and msdb.dbo.agent_datetime(run_date, run_time) = (Select top 1 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime' from msdb.dbo.sysjobhistory where job_id = 'E19D351F-22B8-4624-A8A2-102C094D5E84' order by RunDateTime desc)
order by RunDateTime DESC, instance_id desc
--EXEC msdb.dbo.sp_help_jobhistory @job_name = N'LS_Restore_xyz';
--Get Primary Server, Primary Databases, Secondary Server, Secondary Databases - run on Secondary:
Select primary_server, primary_database, secondary_server, secondary_database
from log_shipping_monitor_secondary
--Get Log Backup Sizes:
SELECT
bs.Server_Name,
bs.Database_Name,
bs.Type,
(bs.backup_size/1024)/1024 'BackupSize(MB)',
bs.Backup_Start_Date,
bs.Backup_Finish_Date ,
bs.Recovery_Model,
bf.Physical_Device_Name,
bs.User_Name
FROM
MSDB..backupset bs
INNER JOIN msdb..backupmediafamily bf ON bf.media_set_id=bs.media_set_id
where bs.type = 'L'
and bs.database_name = 'xyz'
order by backup_finish_date desc
--Get Log Shipping Monitor History Detail:
SELECT
@@SERVERNAME as 'SRV',
Case Agent_Type When 0 Then 'Backup' When 1 Then 'Copy' When 2 Then 'Restore' End as 'Agent_Type',
[Database_Name],
Log_Time,
[Message],
Session_ID
FROM msdb.dbo.log_shipping_monitor_history_detail
Order by log_time desc
--Get Log Shipping Monitor Error Detail:
SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail Order by log_time desc
--Stores Alert Job ID
SELECT * FROM msdb.dbo.log_shipping_monitor_alert
--Stores error detail for log shipping jobs
SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail
--Stores history details for log shipping jobs (Agent session details)
SELECT * FROM msdb.dbo.log_shipping_monitor_history_detail order by log_time desc
--Stores one monitor record per primary database in each log shipping configuration
SELECT * FROM msdb.dbo.log_shipping_monitor_primary
--Stores one monitor record per secondary database in a log shipping configuration
SELECT * FROM msdb.dbo.log_shipping_monitor_secondary
--Stores one record for the primary database in a log shipping configuration
SELECT * FROM msdb.dbo.log_shipping_primary_databases;
--Stores one record per secondary database in a log shipping configuration
SELECT * FROM msdb.dbo.log_shipping_secondary_databases;
SELECT * FROM [msdb].[dbo].[sysjobhistory] WHERE [message] like '%Operating system error%'
SQL Server Reports: Standard Reports:
Transaction Log Shipping Status - view this on the Primary & Secondary Nodes. Details
sqllogship Application:
Primary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):
C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Backup ...
Secondary Node: Log Shipping: SQL Jobs: sqllogship (Application Executable):
C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Copy ...
C:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" -Restore ...
Resources:
sqllogship Application
About Log Shipping (SQL Server)
Change Roles Between Primary and Secondary Log Shipping Servers (SQL Server)
Fail Over to a Log Shipping Secondary (SQL Server)
|
|
|
|
|
|