Solved

SQL 2005 Transaction Log Shipping not working but no errors are reported

Posted on 2009-04-07
4
1,281 Views
Last Modified: 2012-05-06
Hello,
I am trying to use Transaction Log shipping to replicate a SQL 2005 database. The log shipping set up and process proceeds without any errors or warnings but when I test the replicated database none of the transaction logs seem to have been applied to it.

Here are the details:
- Primary server = windows Server 2008 Cluster with SQL 2005
- Secondary server = windows server 2003 (not clustered) with SQL 2005
- Both servers are in the same domain
- I have set up a LogShippiongOut folder on the primary server, shared it out and made sure that SQL server agent has modify access.
- I have set up a LogShippingIn folder on the secondary server and also made sure that SQL server agent has modify access.
- When setting up Transaction Log Shipping in SQL Server Management Studio I have taken all the defaults and under the Restore Transaction Log Tab for the secondary server I have chosen Standby Mode when restoring. (Though the issue also happens if I choose no recovery mode)
- No errors are reported when the log shipping is initiated and the database properly copies over to the secondary server
- Every 15 minutes all the log shipping jobs run without error.
- The transaction logs themselves appear in the both the LogShippiongOut folder on the primary server and the LogShippiongIn folder on the secondary server.
- BUT when I recover the database on the secondary server to test it, none of the transaction logs actually seem to have been applied and it is in the same state that it was when it was originally copied over during the set up.

Any assistance would be greatly appreciated,
Thank you.
0
Comment
Question by:grjitdept
  • 3
4 Comments
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
There should be a job on the target server which applies the logs. Can you check that and see what errprs are reported.
0
 
LVL 1

Author Comment

by:grjitdept
Comment Utility
There are only three jobs that I can see on the target server. One is an LSAlert job that has an error reading "databse has restore threshold of 45 minutes and is out of sync." - which I assume is because it has not been restoring. The other two are LSCopy and LSRestore. Neither one of has any errors and they all read as successful.
0
 
LVL 1

Author Comment

by:grjitdept
Comment Utility
As some added information I stopped and restarted the logshipping from scratch. Now the LSAlert job does not show any errors. Specifically the details of the three logs from the target server are:

LSAlert log
Date            07/04/2009 10:30:00
Log            Job History (LSAlert_SERVER01)
Step ID            1
Server            SERVER01
Job Name            LSAlert_SERVER01
Step Name            Log shipping alert job step.
Duration            00:00:00
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0
-------------------------------------------------------------------------------
LSCopy log
Message
Executed as user: DOMAIN\Service Account. The step succeeded.
Date            07/04/2009 10:30:00
Log            Job History (LSCopy_server02)
Step ID            1
Server            SERVER
Job Name            LSCopy_ server02
Step Name            Log shipping copy job step.
Duration            00:00:02
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0
Message
Executed as user: DOMAIN\Service Account. The step succeeded.
---------------------------------------------------------------
RSResore log
Date            07/04/2009 10:30:00
Log            Job History (LSRestore_ server02)
Step ID            1
Server            SERVER
Job Name            LSRestore_ server02
Step Name            Log shipping restore log job step.
Duration            00:00:46
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0
Message
Executed as user: DOMAIN\Service Account. The step succeeded.

I have changed the names but server01 is the target server and server02 is the primary server. The SQL agent is set to run under the domain Service Account. Again there are no errors being reported but the database on the target server does not seem to be actually have the transaction llogs being applied.
0
 
LVL 1

Accepted Solution

by:
grjitdept earned 0 total points
Comment Utility
As it turns out, I had failed to notice that the transaction logs of the database were being backup by another job.  Once I disabled that job and reconfigured the log shipping it worked fine. It is still strange however that no errors were reported in any of the logs.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now