Log Shipping Monitor in SQL Server 2000 1

[wbodger]

So, I have Log Shippnig setup and running just fine, but when I check the Log Shipping Monitor, it looks like this:

Log Shipping Pair: SQL01.Onyx - StageSQL.Onyx --(correct)
Last Backup: 2007-04-20 10:04:47.960 --(true)
Backup Threshold: 2160 --(true)
Last Copy: 2007-04-23 00:00:07.130 --(true)
Last Restore: 2007-04-23 00:55:37.837 --(true)
Sync Threshold: 2160 --(true)
Alerts enabled: Yes --(true)
Status: Out of Sync (72:48)--(what?!?!?)

So, I know that it is copying and loading the TRN files, but why is it saying that it is out of sync? It looks like it is having a problem because I am not doing a full backup and restore, but I thought that was why I was doing the Log Shipping, so that I would no have to do that resource intensive backup and restore on a rgular basis. Is this something I should just ignore, or did I miss this setting when I setup Log Shipping?

Willie

 

[ptheriault]

Your last trn backup was 2007-04-20 10:04:47.960
Your last file to be restored was 2007-04-23 00:55:37.837.

Are you sure that the backup Tlog job is running?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

Well, the last backup was this morning at 6 am (Onyx_tlog_200704230600.TRN). When I look at the history, the last line is:

Activity: Load
End Time: 2007-04-23 00:55:38.053
Duration: 3336
Status/Error: Succeeded
# Files: 1
Last File: Onyx_tlog_200704220600.TRN

And I see transactions from the weekend on SQLStage, so I know the backup has been working. Now, I only have 2 servers, so the Log Shipping Monitor is running on the destination server (sorry I forgot to mention that earlier), but I don't see how that would cause the problem. I have the monitor running under an account that has suitable permissions on both machines.

Thanks for your help.

Willie

 

[ptheriault]

There is no problem with the monitor being on the destination instance. I have run log shipping like that befor. Have you checked the settings for the monitor? Are you sure the monitor jobs are running? What are your alert thresholds?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

I have looked thru the Log Shipping tables in msdb on the destination server and the only thing that seeems to be out of whack is in the log_shipping_primaries table. The last_backup_filename is first_file_000000000000.trn, whichc doesn't seem right, it seems it should increment with the trn log file names and it says that was last updated 4/20/2007 10:04:48 AM. I went with the default thresholds of 3 times the backup length and I am backing up every 12 hours.

I have found that when I do more frequent transaction log backups, I sometimes ahve transactions running thru the backup time and that seems to cause a problem with the log creation, it doesn't properly close the log and then there are restore issues.

Willie

 

[ptheriault]

By the looks of what you got I don't think your monitor account has access to the msdb database on the prmiary.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

Hmm.... That could be. When I looked at the Log Shipping tables on the primary they were pretty empty, just defining the relationship, but contained no current data.

So, in looking into the logins, I see that I am willieb on one machine and then domain_name/willieb on another with willieb being a standard account and domain_name/willieb being a Windows User, but when I try to add a domain account to teh primary (where it has willieb right now) it tells me the user already exists. So, if willieb ad domain_name/willieb have the same permissions on their respective servers, shouldn't it work?

 

[ptheriault]

I would drop the sql/willieb and add the domain/willieb. Then set the permissions. I think you will have trust problems if you don't.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

OK, I have adjusted the logins accordingly, do you think I need to rebuild the Log Shipping for those changes to take effect on the Log Shipping Monitor? I originally told it to use the windows login I was using. And again, thank you for your help on this.

Willie

 

[ptheriault]

no you don't have to rebuild log shipping.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

OK. I will just return to moitoring it for a while and see how things turn out. Thank you very much for your help.