Log Shipping Restore seems to Time out after 10 minutes

While playing with log shipping it appears that the restore is timing out, without errors. The shipper is successfully backing up to disk, xcopying the backup to the receiver, calling the restore SP on the receiver. The restore runs for exactly 10 minutes
and 1 second then ends with a success. Any ideas on what I missed?
Denny,
I'm not sure about this. How do you know it has timed out - do any of the
transactions get applied on the standby server at all?
Regards,
Paul Ibison
|||I am not sure it is timing out, it was only a guess. When it tries to apply the first log it says that database was only partially restored.
If I manually run the retore SP on the receiver, rather than having the shipper call it, it successfully restores the entire backup file then I can ship the logs with no errors.
|||I ran the restore SP via Query Analyzer on the shipping server and I was correct. The error was (OLE/DB provider returned message: Timeout Expired)..
Did I do my linked server incorrectly?
|||Denny,
your setup is sound, as there is no timeout property in log shipping. I
suspect this is something peripheral - it could be network lag, or
alternatively (and possibly more likely) your database is expanding. I have
a KB article that explains this as a potential cause of this issue:
http://support.microsoft.com/default...;EN-US;Q305635
HTH,
Paul Ibison
|||I was able to narrow it down to a timeout setting with the Linked Servers. Both the connection timeout and query timeout had a value of zero entered, which is supposed to be no timeout (assuming I understood the documentation correctly). As soon as I set
the query timeout to 1200 seconds everything works fine. I will probably set it to 1800 seconds, I'd imagine 30 minutes would be more than enough time to restore from a file.
|||Denny,
but log-shipping doesn't use linked servers. Is this perhaps a custom
inplementation?
Regards,
Paul Ibison
|||Yes it is. I followed the directions from a doc on SQL-Server-Performance. The scheduled task on the production box dumps to disk, copies it to the warm standby server using xcopy, then calls an SP on the warm standby server that restores that file.
|||Denny this makes sense now. If the query timeout is zero, it uses the value
in sp_configure. I thing that you'll find that that is the problem as it is
set too low. Setting the value at the lower level as you have done overrides
the sp_configure value, so that explains why it works now.
Regards,
Paul Ibison