Log Shipping Monitoring and Troubleshooting

Log Shipping Monitoring and Troubleshooting
交易紀錄傳送監控與疑難排解

1.Log Shipping status
View the Log Shipping Report (SQL Server Management Studio)
https://msdn.microsoft.com/en-us/library/ms181149.aspx

To display the Transaction Log Shipping Status report on a server instance

1. Connect to a monitor server, primary server, or secondary server.
2. Right-click the server instance in Object Explorer, point to Reports, and point to Standard Reports.
3. Click Transaction Log Shipping Status.

sp_help_log_shipping_monitor (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187820.aspx

 sp_help_log_shipping_monitor

Remarks
sp_help_log_shipping_monitor must be run from the master database on the monitor server.

Permissions
Requires membership in the sysadmin fixed server role.

找出Monitoring Server方法

 SELECT monitor_server FROM msdb.dbo.log_shipping_primary_databases;

or

 SELECT monitor_server FROM msdb.dbo.log_shipping_secondary;

2.Job History

View the Job History
https://msdn.microsoft.com/en-us/library/ms181046.aspx

     To view the job history log

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, and then expand Jobs.
3. Right-click a job, and then click View History.
4. In the Log File Viewer, view the job history.
5. To update the job history, click Refresh. To view fewer rows, click the Filter button and enter filter parameters.

or

 -- lists all job information for the NightlyBackups job.  
USE msdb ;  
GO  

EXEC dbo.sp_help_jobhistory   
    @job_name = N'LSRestore_ServerName_DBname' ;  
GO

3.Backup and Restore History
Backup History and Header Information (SQL Server)
https://msdn.microsoft.com/en-us/library/ms188653.aspx

Query SQL Server backup history and restore history records
https://blogs.msdn.microsoft.com/bradchen/2014/03/12/query-sql-server-backup-history-and-restore-history-records/
4.ERRORLOG
View the SQL Server Error Log (SQL Server Management Studio)
https://msdn.microsoft.com/en-us/library/ms187109.aspx

1. In Object Explorer, connect to an instance of the SQL Server and then expand that instance.
2. Find and expand the Management section (Assuming you have permissions to see it).
3. Right-click on SQL Server Logs, select View, and choose View SQL Server Log.

5.Event log
Start Event Viewer
https://technet.microsoft.com/en-us/library/cc766401(v=ws.11).aspx

To start Event Viewer by using the Windows interface

1. Click the Start button.
2. Click Control Panel .
3. Click System and Maintenance .
4. Click Administrative Tools .
5. Double-click Event Viewer .

To start Event Viewer by using a command line

1. Open a command prompt. To open a command prompt, click Start , click All Programs , click Accessories and then click Command Prompt .
2. Type eventvwr .

Reference:
Monitor Log Shipping (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms190224.aspx

Bring Database back to norecovery mode from standby mode(Standby / Read-Only)

 RESTORE DATABASE [AdventureWorks]
WITH NORECOVERY;

Adding a log shipping monitor
https://www.sqlservercentral.com/articles/Log+Shipping/77295/