Log Shipping is a feature in SQL Server by which you can ship transaction log backups to a different server and restore the backups onto a standby database for disaster recovery or reporting purposes.
One of the major fundamental differences in SQL Server 2000 and SQL Server 2005 log shipping is that SQL Server 2005 uses linked servers to communicate between the primary and monitor and the secondary and monitor. The log shipping jobs are executed via linked server queries to update information about the backup, copy and restore jobs in case you have a remote monitor server.
So, if you find that your log shipping reports are defunct, then two additional things that you can do apart from the basic log shipping troubleshooting steps are:
1. Check if remote connections are enabled on the primary, secondary and monitor. For SQL Server 2000, check connectivity between the instances.
2. Check if a linked server using the same authentication settings as your log shipping setup can be setup between the primary and monitor or secondary or monitor depending on which part of your log shipping setup is broken. This is again applicable for SQL Server 2005.
Basic Log Shipping Troubleshooting Steps
1. Look into the SQL Server ERRORLOGs, Application/System Event Logs for any errors related to log shipping. Check the job history of the log shipping Backup/Copy/Restore jobs for any errors.
2. Check the Backup, Copy & Restore job history details for any errors.
3. If you are using SQL Server 2005, check what details are being displayed in the Log Shipping Report under Standard Reports in Management Studio.
4. If you want to take your troubleshooting to the next level, then you can even look into the log shipping meta data by querying the log shipping tables on the primary/secondary/monitor(if configured).
Addendum: April 26th, 2011
The log shipping configuration information can be found using the following methods:
1. Standard Reports – Transaction Log Shipping Status (right click Server Name in Management Studio-> Reports -> Standard Reports -> Transaction Log Shipping Status)
2. Use the Stored Procedure which is called by the above report: EXEC master..sp_help_log_shipping_monitor
Query to check log shipping job errors using the MSDB log shipping system tables
--List of Log Shipping jobs SELECT * from dbo.sysjobs WHERE category_id = 6 SELECT * FROM [msdb].[dbo].[sysjobhistory] where [message] like '%Operating system error%' order by [run_date] , [run_time] SELECT * FROM [msdb].[dbo].[log_shipping_monitor_error_detail] where [message] like '%Operating system error%' SELECT * FROM [msdb].[dbo].[restorehistory]
Known issues with Log Shipping
1. You might find that the last backed up/copied/restored files do not reflect correctly in the log shipping reports when you use a remote monitor server. In such a scenario, check if the following issue documented in the blog post below is applicable in your case:
The last copied and restored file will show up as null if the monitor instance is not on the same box as the secondary instance. The last backed up file will show up as null if the monitor instance is not on the same box as the primary instance if the select @@servername value is not used as the monitor server name while configuring the log shipping monitor.
2. If “REMOTE ACCESS” (sp_configure will show if it is enabled or not) is not enabled or the LOG SHIPPING LINKED SERVER (to the monitor server) is not working for the primary and secondary servers, then last backup file/last copy file/last restored file information will not get populated if a remote monitor server instance is being used. The easiest way to identify this issue would be to capture a profiler trace (on primary instance when the backup job is running and on the secondary instance when the copy/restore job is running). The profiler trace will report errors if an update operation pertaining to the log shipping monitor tables fails provided all “Errors and Warnings” profiler events are captured.
3. Another issue that you could run into while using Log Shipping is Orphaned Users if you have Database Users on the Primary Database mapped to SQL Authenticated Logins. This happens because the SIDs of the SQL Authenticated Users on the Primary and Secondary instance would be different. I documented the workaround to this issue in the following blog post: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/13/orphaned-users-with-database-mirroring-and-log-shipping.aspx
4. When you are scripting out an existing log shipping configuration, ensure that you have Cumulative Update Package 9 applied for SQL Server 2005 Service Pack 2 applied for Management Studio. If that is already done, then use one of the options mentioned in the more information section in the KB Article below:
955693 FIX: In SQL Server 2005, the file information about the transaction log that was last copied and the file information about the transaction log that was last restored are missing
5. If you have configured LogShipping with STANDBY mode on SQL Server 2008 and the destination folder for the TLOGS uses a remote server (on which the sqlservice/sqlagent is not a Local Admin), then the restore job will fail everytime with following error :
2008-12-12 14:44:58.53 *** Error: During startup of warm standby database ‘testdb’ (database ID 7), its standby file (‘<UNC path of the TUF file>’) was inaccessible to the RESTORE statement. The operating system error was ‘5(Access is denied.)’.
TUF = Transaction Undo File which is required for applying the next T-LOG backup. This issued is fixed in the cumulative update mentioned in the KB Article below:
FIX: Error message when you use log shipping in SQL Server 2008: “During startup of warm standby database ‘<Database Name>’ (database ID <N>), its standby file (‘<File Name>’) was inaccessible to the RESTORE statement”
6. Log shipping restore will fail if there is a snapshot or an active DBCC replica on the secondary database on which the restore is being done.
Addition: September 12, 2012
In case you need to speed up the transaction log restore for your log shipping secondary database in standby mode, then follow the steps mentioned in this post.
In case you need to move your secondary log shipped database files to a new physical location, then you can use the steps mentioned in this post.
Hi Amit, We just encountered a weird issue where Transaction Log Shipping status shows no backups happening on primary while its indeed happening(we checked msdb,LS backup jobs etC) But Once we update the Log Shipping Monitor Settings to use sql authentication (It was using windows auth impersonating proxy account of the job)those errors/alerts magically seem to disappear from the Transaction Log shipping status.
Have you seen this issue?
It looks like the last backed up file information is not being updated in the log shipping tables using the windows auth proxy account but it is able to update the tables using the SQL autheticated user. You would want to check for double hop issues and login failed messages for the windows account when the backup job runs. The last step of the backup job is to update the backed up file information in the log shipping history table. Use the Log Shipping standard report in Management Studio to identify if the last backed up file is being updated correctly when the windows account is used to run the job. My guess is NO.
Hi Amit, Thanks, Yes, the last backed up file is not being updated, and yes it ended up being a permission issue, monitor server had issues talking to primary machine.
last few days facing one prouble in logshipping.
PROB : Log Shipping Jobs are not Alerting us to failures, even logshipping is not writting any application logs? Please help me if with your sigguestions?
for the above issue how can we reslove what are steps we have to take please guide me
Check the log shipping alerts and find out on what alert condition you have set them to fire. By default, the log shipping alerts (if setup) fire only after a configured latency is observed. Also, if the log shipping jobs fail, an alert response has to be configured for the jobs for you to be notified. If this is already done, then you need to investigate by collecting profiler traces to check what errors are being observed when the alert is being fired.
Check my previous response.
As this are prod servers we cannot setup profiler trace, checked alret job details and looks fine everything.. do we need to Flag any logon to LS_Alert jon if we have failure? if so how we need to check and trouble shoot..
There should be an alert response configured for the log shipping alerts defined. In case this is a critical production related issue, I would suggest contacting Microsoft Support so that they can work with you to determine if there is a problem with the existing alert configuration.
Pingback: A year that was « TroubleshootingSQL
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 6490058000000225800001, which is too late to apply to the database. An earlier log backup that includes LSN 6489983000000041500001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
i have got this error.I know to apply t-logs to apply in seqeunetial but whta to do if t-logs are missing.
then apply recent full backup and recent differential and recent t-logs is enough right
plz leave a reply for thisss.
If you have missing t-log backups, then there is no way to recover from that scenario. The only option is to restore upto the point where the next t-log backup in the chain is missing. Or to recreate the log shipping secondary from the latest available backups.
I am getting following error when i run sp_help_log_shipping_monitor this command
OLE DB provider “SQLNCLI” for linked server “LOGSHIPLINK_x.x.x.x_-1654363917”
returned message “No transaction is active.”.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
when I generate transactional log shipping report it is giving me severe error occoured.
I have configured log shipping on same server
Is your log shipping working correctly? If yes, then could you check if any error numbers are being reported like 7391 when you get the above error? This could be a problem with the DTC configuration on the server.
Pingback: Interview Questions SQLServer DBA | vijaysen
Thanks Amit for sharing this informative article with us it helps me a lot to understand the log shipping issue. I also have found an article that gives some more information on the same topic. Must see form here: http://www.sqlserverlogexplorer.com/sql-server-transaction-log-shipping-and-backup/
Pingback: How To Check The Log Shipping Status | Health Deal