Backup BizTalk Job not able to backup databases which reside on the different SQL server

  • Article

I recently came across an issue where my backup BizTalk job was not able to back up DTA and few other databases which reside on a different SQL server. I noticed that my job was failing with the below error message each time it was trying to complete.

Error Message:

After 10 minutes of operation getting following error
Server: Msg 7221, Level 18, State 2, Line 5
Could not relay results of procedure 'sp_BackupBizTalkFull' from remote server ‘Server Name’.
[OLE/DB provider returned message: Timeout expired]  

I realized that backing up the databases from the other SQL instance was taking more than 10 mins to finish. And as we all might be aware of the fact that the other SQL instance would allow remote queries for 10 mins only. So even before my remote query can complete, other SQL instance was stopping this query and throwing the above error message.

To avoid this situation, I changed the remote query timeout value on the other SQL instance and allowed it to execute remote queries without the time limit.

After this change, back up BizTalk job never failed and the other SQL instance started allowing remote queries to run beyond 10 mins.

You can change the above setting on the SQL server by following the below steps:

  1. In the object explorer of SQL server management studio, right click the server and select properties.
  2. Click the Connections node.   
  3. Under Remote Server Connections, in the Remote query timeout box, type 0. 0 means there is no timeout applied for the remote queries.

Better Approach

Allowing the remote queries to run without any timeouts, would resolve your issue. But if there is any problem with some other query in future, it would never time out and you would never come to know about what is wrong on the SQL server.

The better approach to tackle this problem would be to initially change the remote query timeout value to 0. After doing this, run the query which was having this problem and take a SQL profiler trace simultaneously. Using this SQL profiler trace, you would come to know how long the query actually took to complete and you can change the property accordingly.

 

I hope this help!!!

Written by
Yakshit Gohel

Reviewed by
Chirag Pavecha

Microsoft India GTSC