November 6, 2006 at 8:24 am
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
November 7, 2006 at 8:01 am
Hi, if you dont get any other response or you need to fix quickly, I have had a few issues in the past with setting up log shipping and found it far easier to just set it up again from scratch!! (Having removed it properly first via the DB Maint plan - remove log shipping button) HTH
November 7, 2006 at 9:25 am
Did you check the log shipping jobs on the primary and secondary servers? Are any of them failing? (Transaction log backups, file copies, restores, or alert jobs.) The job histories should help. If none of them failed, you might need to adjust the out-of synch threshold (Log Shipping Monitor properties, Destination tab, Alert Threshold). If none of these help you may need to set up Log Shipping from scratch. Since you "manually updated the records" you might have made matters worse. Hope this was helpful.
November 7, 2006 at 9:48 am
All of the jobs are fine, I only updated one of the db records on the primary the problem seems to be that the monitoring table are not updating with the latest info from the history.
November 7, 2006 at 10:09 am
How often does Log Shipping run? Out-of-synch alerts can be a sign that it's just behind somewhere. Can you manually run the Log backup job, the copy job, and the restore job? Check the Monitor after running each to see if it is updating properly.
November 7, 2006 at 10:30 am
the cycle runs every 15 minutes with each job stagered by five minutes this is not any issue of one being off, when monitoring the jobs I can see they are completing without a problem. Upon running the jobs manually they run successfully but the monitors do not reflect this.
November 7, 2006 at 10:59 am
Few more questions. Have permissions changed anywhere (in SQL Server or on the servers themselves)? When you go into the Monitor do you get any errors when you click "view backup schedule", "view copy schedule", or "view load schedule"? You could also run Profiler to see what might be failing....
November 7, 2006 at 11:37 am
Nothing has changed in terms of permissions. The monitor job is failing on the monitor server as the databases have breached the time frame. This causes the entire job to fail (I think this is a real defeciency in sql 2005). For that matter in my experience if one database fails the entire job fails and this is probably the root of the problem.
November 7, 2006 at 12:02 pm
I have the same issue. Databases are in sync primary -> log-shipped copy & there are no failures of copy/restore on the target. If you look at properties, you can see that logs were applied successfully. The monitor just stopped logging the activity.
Even after resyncing, this is still the case. I think that it might be a bug with particularly large databases (>20GB). Have seen this before at other installations. The smaller db's are fine, but the larger ones get these false "OOS" readings.
Regards, Melissa
November 7, 2006 at 1:30 pm
One of the eight db's that I have on the box is over 100 GIG. All databases on the box are effected however. Are you using sp_refresh_log_shipping_monitor to resync?
November 7, 2006 at 5:08 pm
Are the jobs using Windows Authentication or a SQL account to update the monitor? If using a SQL account and the password has changed, you will need to update it in the log shipping configuration.
November 8, 2006 at 8:02 am
Try stopping then starting the SQL Server agent on the monitor server. If that doesn't work, do all three.
November 9, 2006 at 8:09 am
Probe uses sql-authenticated account. I actually had to resync again yesterday, WITH a remove/recreate of log-shipping (side note: I changed dump directory names on the primary, and modfied the logshipping maintenance plan accordingly and did a stop/restart of the agent jobs before/after, etc.; but, the copies to the target just wouldn't go ... no failure, just no copy). So, changed the log_shipping_monitor_probe account pw as part of the recreate & resync, and now everything is in sync.
BTW, is that sp_refresh_log_shipping_monitor stored proc undocumented? Can't find it in either msdb or master.
Regards, Melissa
November 9, 2006 at 8:39 am
I am working with slq 2005 and is run from master.
November 13, 2006 at 2:36 am
Hi,
Did you use the corresponding server name/IPAddress for each of the primary and secondary servers?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply