Log shipping in SQL 2005


I have recently set up log shipping in MSSQL 2005 (sp3 cu7) using 2 SQL instances and a primary DB on instance 1 log shipping to a secondary database on instance 2. I have the secondary database in no recovery mode as I simply don;t want anything change the database. However it keeps failing after a few hours and I would like to ask some questions to make sure I have the environment is set up properly. I don't have any problems setting up the log shipping but it fails only after a few hours and I get the following error messages:

Error: Could not find a log backup file that could be applied to secondary database

The log shipping secondary database INSTANCE.SECONDARY has a restore threshold of 45 minutes and is out of sync. No restore was performed for 504 minutes. Restored latency is 10 minutes. Check agent log and logshipping monitor information.

My question are:

1 - Do I run maintenance plans on the primary server now I have log shipping essentially backing up the TRN files on the primary database? Does log shpping truncate the primary databases TRN files?

2 - What happens if I run maintenance plans on teh secondary database? Would this have impact on log shipping?

I have a feeling that the LSN is out of sequence as another backup job has run, but I just don't know how to get this working right.

Any suggestions?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Hello, unfortunately, you can not perform a transaction log backup during the logshipping. However a scheduled job or maintenance plan makes a transactional backup and the logshipping stops to work, then you should restore this backup manually at the instance 2 (with norecovery and stand by). After that, the logshipping can be countinued automatically.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gladminsAuthor Commented:
>> Hello, unfortunately, you can not perform a transaction log backup during the logshipping.
When you say this, does this mean you can't perform a transactional log backup on the primary database or the secondary database?

I have a maintenance job that backs up all databases on the secondary server, but the database doesn't appear in the maintenance plan list of databases when I select no recovery mode so I assumed that the secondary database that is configure for log shipping would not get backed up. Does this make any sense?
Máté FarkasDatabase Developer and AdministratorCommented:
Physically you can take a backup on a logshipped primary database, but this backup disorders the sequence of logshipping backups (because the logshipping is a simple sequential transaction log backup and restore procedure).
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

gladminsAuthor Commented:
ok - thanks for you help. I think I'm getting there. So do I need to run transaction backup maintenance plans on the promary database so to keep the transacation log (ldf) file size down?

So in essence I cannot take a backup of the primary or the secondary database?

Even if the datasbe is set to full recovery mode, the transactional log file, ldf, will be maintain byt the log shipping job?

If you can answer these questions then I think you've nailed it and the points
Máté FarkasDatabase Developer and AdministratorCommented:
Yes, the logshipping procedure will maintain the size of the log file (because I has already backed up the log), so you do not need any additional transactional backup on it (it makes it more complicated).
You can make a COPY_ONLY full backup on the primary database if you want. Just ask me if something is not clear yet.
gladminsAuthor Commented:
no that's perfect. Where I was falling down was I had a log file maintenance plan backing up the primary database which put the LSN number out so the restore of the TRN files to the secondary database were out of sync. I didn't trust the log shipping job to truncate the primary database LDF file but when you think about it, log shipping is a glorifed maintenance plan really.

thanks for you help and i hope this helps someone else!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.