grjitdept
asked on
SQL 2005 Transaction Log Shipping not working but no errors are reported
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.
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.
St3veMax
There should be a job on the target server which applies the logs. Can you check that and see what errprs are reported.
ASKER
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.