Link to home
Start Free TrialLog in
Avatar of gladmins
gladmins

asked on

Log shipping in SQL 2005

Hi,

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?

Cheers
Gladmins
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gladmins
gladmins

ASKER

>> 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?
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).
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
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.
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!

Cheers