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?
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.
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).
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.