September 18, 2008 at 9:03 am
Hello,
I have 2 SQL 2005 servers with log shipping.
I was wondering how to be able to check if the logshipping was running fine. So after a liitle searching on the internet, I found some stored procedures at Microsoft (http://msdn.microsoft.com/en-us/library/ms190224.aspx)
But when I run the sp_help_log_shipping_monitor_secondary 'SecondaryServer\Sales2', 'Sales'
command on the primary server, I see that in the last copied file is one that dated from over 6 months and last_restored_file and last_restored_date are NULL.
When I run the
sp_help_log_shipping_monitor_primary 'PrimaryServer\Sales', 'Sales'
command on the primary server, it says that the last copied file is todays.
When I ask the report on the Primary server it says that the status of both my primary and secondary databse is "Good".
Anyone had the same problem or know how I can be sure that the secondary server is up to date?
Kind regards,
Peter
September 18, 2008 at 9:48 am
A very simple way to check your logshipping is working is to make a small change in one table on the primary and see if it appears on the secondary 😀
September 19, 2008 at 7:33 am
You can query several tables, such as log_shipping_monitor_primary and log_shipping_monitor_secondary. However, these likely will just reflect what the scripts from Microsoft are telling you.
It seems like your monitoring server is not being updated. If that's the case, log shipping itself might be fine. Check the job history of the log shipping jobs on both servers to see if any have failed. If they are failing consistently, you may have to re-setup log shipping. But be patient, log shipping is quite robust and often catches up automatically after a few runs.
If the jobs are working without failure on both servers, then take the next steps: (it's best to do these off-hours or at a time when log shipping does not run)
1) Find the right business user for the database being log shipped. Get at least 3 reports or queries to run. Run these on the primary database. Be careful to record the parameters and date ranges.
2) Stop the SQL Server service on the secondary server (standby server).
3) Copy the .mdf and .ldf files to another name
4) Start the SQL Server serice on the secondary server (log shipping will catch up by itself, just give it time)
5) Attach the copied .mdf and .ldf files as a new database
6) Run the exact same reports/queries with exactly the same paramenters. Compare the results of the two reports. They should match exactly.
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply