How to resolve timeout issue while taking SQL Server backup and what is the demerits of making SqlCommand.Timeout property to infinity and is there a way to avoid?

Question

Hi

Currently, I am doing a small research about taking backups from SQL Server using external tools that execute backup queries to take backups. Usually, the tool utilizes SqlCommand to execute backup queries along with checksum for performing backup could take the back for customer systems. However, in some scenarios, this backup gives SQL Server Timeout exception while taking a backup of huge SQL Server databases like 6GB or more. Upon investigating I found that the default timeout for SqlCommand.Timeout was 30 seconds which can be resolved to a higher limit where the backup can take place. This problem will again arise when the database size is high, so what should we do with even bigger databases? Is there any feasible solution without changing every time this property? Also upon further research, I found the timeout to be set to infinity by setting SqlCommand.Timeout=-1 which will solve all the problems. The next question arises from it, is there any serious problem(s) that may arise in the future and what are those? And is there a way to avoid those altogether like having infinite time without adverse consequences on systems?

Regards

Answer 1

To resolve timeout issues while taking SQL Server backups, you can increase the SqlCommand.Timeout property value to a higher limit. However, this problem may still arise when the database size is large. Setting SqlCommand.Timeout to -1 (infinite) can solve the problem, but it is not recommended as it can lead to adverse consequences on the system. Instead, it is recommended to troubleshoot and improve the performance of the queries. By far, the most common reason for query timeouts is underperforming queries. Making the query run faster is the recommended first target of your troubleshooting. You can use Extended Events or SQL Trace to identify the queries that cause the timeout errors and execute and test the queries in SQLCMD or in SQL Server Management Studio (SSMS). If the queries are fast in SQLCMD and SSMS but slow on the application side, change the queries to use the same SET options used in SQLCMD and SSMS. If you expect the processing queries to take more than one hour, raise the ExternalCommandTimeout setting higher than one hour.

---

References:

• Troubleshoot query time-out errors
• Troubleshoot operational issues with the SQL adapter

Answer 2

Hi，Smruti Ranjan MohapatraWelcome to Microsoft Q&A fourm！

Typically, a full data backup is the most time-consuming of all backup measures. For some large databases, waiting for an hour can be normal. The backup time is also related to the busy level of the server operation. Therefore, it is usually not chosen to frequently perform a full backup of the database. Certainly, setting SqlCommand.Timeout = -1 is an effective way to solve backup timeouts, but using production database resources for a long time for large backups is a risky method that can easily cause resource bottlenecks. Setting an appropriate timeout value can reduce the blocking time to a manageable expectation. Alternatively, you can choose an appropriate time to perform the backup operation. I don't know what strategy your third-party tool uses or if it can be configured. You can shorten the backup time by compressing the backup or by adopting other backup strategies, such as Differential backups、Transaction log backups.

Answer 3

The default timeout of 30 seconds is a hole in the head, in my opinion. (And not a very humble one in this case.) The only reasonable default is to wait forever, using -1. (I believe 0 will also work.) When you need a timeout, you should set one explicitly.

In this case, the timeout can only cause you grief. Sure, the back could be stalled, but that could be the case also if you run the backup from SSMS. And guess what, SSMS sets the timeout to -1!