Solved

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

Posted on 2009-04-07
4
1,289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24084703
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
ID: 24084893
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
ID: 24085434
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
ID: 24105984
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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