Ive setup log shipping for two of my databases to a server so I can run reports against the 2nd server. Once I set it up, the process runs quite happily for 3/4 days then the restore process of the logs to the secondary server starts to fail. The copying of the files is still working its just the restore side which is failing. The only way to get this back up and running is to remove the log shipping options and resetup the process.
How can I identify the problem? Ive checked the job history of the restore process and this is the error.
Date 08/03/2011 10:10:00
Log Job History (LSRestore_HOGWARTS_GoldMine)
Step ID 1
Server CLONER
Job Name LSRestore_HOGWARTS_GoldMine
Step Name Log shipping restore log job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CLONER\SYSTEM. The step failed.
Microsoft SQL Server 2005
Last Comment
daiwhyte
8/22/2022 - Mon
Daniel_PL
I suppose you are shipping your logs with standby option.
When your log shipped database is used in some queries no logs can be restored. Maybe this is your issue?
I'd suggest you to think about mirroring as an option. You can mirror your database and in mirror server run snapshots against mirrored db. Then you can generate raports using snapshot.
Take care,
Daniel
daiwhyte
ASKER
Ive gone through more of the job history and found this which could have something to do with the issue.
Message
2011-03-08 11:44:28.12 *** Error: The file '\\cloner\db2\UpdateGM_20110305113001.trn' is too recent to apply to the secondary database 'UpdateGM'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-03-08 11:44:28.12 *** Error: The log in this backup set begins at LSN 7634000000012500001, which is too recent to apply to the database. An earlier log backup that includes LSN 7632000000345400001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
I think you need to determine the best value by yourself.
What I can suggest is that you need to observe when you database is in use - how long does it take?
Next you need to determine how long does it take for your server to restore unrestored logs.
You need to keep logs as long as they are not restored.
Maybe scheduling reports execution to low transaction time in your enviroment will help - if you can schedule them like that ;)
Daniel_PL
Let me correct myself:
You need to keep logs at least as long as they are not restored ;)
daiwhyte
ASKER
Ok, that makes sense. I run a report on Friday at 1 and its at this point the log shipping falis. Im going to change it so the log shipping doesnt take place during that time.
When your log shipped database is used in some queries no logs can be restored. Maybe this is your issue?
I'd suggest you to think about mirroring as an option. You can mirror your database and in mirror server run snapshots against mirrored db. Then you can generate raports using snapshot.
Take care,
Daniel