SQL Server Performance Forum – Threads Archive

Log Shipping Fails Not Sure Why….

I set up log shipping through the SQL server enterprise manager GUI. It successfully created the database on the secondary server (I did select the option where the secondary server can become a primary). However, it does not appear that it is successfully importing the transaction logs. It looks like the transaction logs are being backed up fine on the primary server. It also looks like the transaction logs are being successfully copied from the primary to the back up. I assume that the failure has to do with the import. I’m not sure how to find out *why* it is failing. I’ve been looking through the logs and haven’t found anything interesting. I found the job definition: EXECUTE master.dbo.xp_sqlmaint ‘-LSRestorePlanID "8DA5EC10-2B44-4818-8579-C5332999A7EB"’ I tried running this from the SQL query analyzer and it gave me an error about sqlmaint.exe failing. Any suggestions on debugging??

I did notice, if I switched to the grid view that there was this message: GIMP = primary SQL server
SQL-02 – secondary server NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194
Copyright (C) Microsoft Corporation, 1995 – 1998
NULL
Logged on to SQL Server ‘SQL-02’ as ‘[NT CREDENTIALS]’ (trusted)
NULL
Starting load for plan GIMP.whichdoyoulike_logshipping
NULL
Source database – whichdoyoulike
Destination database – whichdoyoulike
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
Loaded 0 files
NULL
Finished load for plan GIMP.whichdoyoulike_logshipping

Its a rule while restoring the database should be in exclusive use and no other process should access the database. Take help from this articlehttp://www.sql-server-performance.com/q&a37.asp by Brad to kill all user connections on standby server when log is restored. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

How ironic — my attempts to monitor the success of the restore were what was responsible. It looks like it is working now. Next step — figure out what it takes to start using the backup server as the primary in case the primary suffered a catastrophic failure. Thanks

Aggh. It looks like when I go to the secondary machine under jobs and run the log shipping restore job, it works, however when it runs automatically (every 15 minutes), it fails. When I look in the ERRORLOG file on the secondary machine, I only see entries for when I manually ran it.
The ERRORLOG on the primary machine only complains once the databases are out of synch for more than X minutes. Any suggestions for how to go about debugging this? Thanks

To overcome the situation firstly stop the log shipping and restore full backup from primary to secondary server. Then re-start Log shipping process and ensure there are break-ups in file passing and restore on secondary server. Due to intermittent errors on secondary server it fails to restore the log. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1289 for similar issue.
Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN and FirstLSN fields that are returned by the RESTORE HEADERONLY command. Some of the most common reasons are:
– There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken.
– There are non-logged operations performed in the database.
-The recovery model of the database was probably toggled between transaction log backups.
-The Data Transformation Services (DTS) task on the primary server might be causing this problem. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>