November 7, 2007 at 10:02 am
The Log shipping Copy & Restore Jobs on a pair of servers are failing randomly. (Sometimes they fail and other times they succeed).
The jobs take only about 1-2 sec to run when they succeed and log files being copied are ~ 150KB.
The files are being copied from datacenter in IL to a datacenter in CT.
It appears that the jobs don't even run (0 sec in the job history) when they fail. No error messages recorded in the SQL Logs or event viewer. They fail for a certain period of time and then run successfully (automatically) on the next run.
I am curious as to why this is happening and if it can be fixed.
Any inputs are appreciated.
Thanks
November 7, 2007 at 3:38 pm
The first place I would look is connectivity between the two servers. If Server A cannot connect to Server B, then the Log will not ship.
The second? See if there was anything to ship. If no changes were made between shipments, the log shipped would be miniscule (I'm not sure how small - probably depends on your row size or something).
November 7, 2007 at 5:56 pm
whats your scheduled backup and restore threshold interval????
"-=Still Learning=-"
Lester Policarpio
November 8, 2007 at 8:17 am
The interval for Log Shipping is every 15 mins..
The funny thing is the local transaction log backup jobs for other Databases on the destination server are failing randomly , like the log shipping jobs .. its as if the jobs dont even run (show 0 sec run time) and if I try executing the Job from the Query Analyzer, I instantly get a
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
There is nothing recorded in the Error Logs or Output Log Files (indicating the Job doesnt even run).. But at other times the same Job runs successfully. For eg, The Job ran successfully at 12 AM, 3 AM, failed at 6 AM, ran successfully at 9 AM and so on ..
This seems a bit strange .. Is this normal behaviour, even if there is nothing to backup ? Or some bug with the server ?
November 8, 2007 at 10:45 am
No, this is not normal behavior. I would expect an error *somewhere* - SQL logs, windows logs, something.
You have checked the windows event logs, haven't you?
November 8, 2007 at 1:12 pm
Yes, there are error messages logged in the event viewer
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'User DB Transaction Log Backup'' (0x92F67FF98761814EB05AC31CA1A4A562) - Status: Failed - Invoked on: 2007-11-08 15:00:05 - Message: The job failed. The Job was invoked by Schedule 44 (Schedule 1). The last step to run was Backup Log.
But this doesn't help much. Why is the Job failing intermittenly while succeeding at other times is the question ..
Thanks
November 8, 2007 at 6:27 pm
Hmmm a bit strange but also happened to me the first time i setup a log shipping with a copy and restore threshold of 15 minutes. In my case the job also intermittently runs ok and failed the reason why (in my case) is that when setting up a log shipping in large databases the transaction log that is saved in the secondary server is not restored in time. Lets say i have a tlog backup of 8:00 AM,8:15 AM, 8:30AM and so on and so forth. If the time now is 8:30 AM in cases of large databases it is not actually synchronized to the point that every tlog saved in the secondary folder is restored on time. GOing back to my example your secondary server maybe restoring the 8:00 AM tlog backup even the exact tym now is 8:30 AM or 9:00 AM surpassing your threshold of 15 min (secondary server is restoring 8:00 AM tlog backup its already 9:00 AM so 1 hour interval). what i did is i change the threshold to 45 min and it works fine well again this is in my case dont know if this is your's also or maybe there is actually other problems in your log shipping.
"-=Still Learning=-"
Lester Policarpio
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply