asked
SQL job failure due to timeout
Hi All, I have some jobs (in the form of .EXE executions) which connect to 2 or sometimes 3 SQL servers (all SQL 2005) However, these jobs seem to fail occasionally (every alternate day) due to errors similar to the one below: **Error messages (at different times):** 1. An error occured at database level : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 2. An error occured at database level : System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding The job runs some stored procedures. The server on which the databases and instance is located have the below memory allocation: Instance 1 - 750 Instance 2 - 700 Instance 3 - 128 Max RAM on server: 3.75GB The connection is made using connection strings. The job is executed from one server and connects to SQL instances residing on another physical server. Questions: 1. Can the timeout errors be related to low memory allocation ? 2. If not, what may the reason of the error?
Have you checked what waits show up in SQL Server? (sys.dm_os_wait_stats)
Simple check - how long do the stored procedures take to run individually - is this longer than your timeout setting. Attempting to run 'jobs' from application code like this needs to handled slightly differently than when you are calling a SP as part of some immediate process.
The reason for the timeout is due to the fact that your connection's timeout parameter (whatever that is) has been reached. There could be multiple reasons why the process takes long enough for the timeout to occur. Memory could definitely be a reason, but so could locking or I/O lagg time etc. be the reason. Without monitoring your server(s) while the process is in fact running, it would be very difficult to pinpoint the reason. You could also consider simply adjusting the timeout of your connections...if the timeout is set to 60 seconds, it would definitely not hurt increasing that parameter.
There are a lot of reasons for a time out: IO, Network or CPU bottleneck, not enough RAM, etc... but I've found that the 1st step to resolve those is focusing on queries or Store Procedures that use IO the most. Such approach not only improves your queries and execution time, but it will also reduce IO utilization and release wasted space or pages from RAM. The end result? less time outs over the time. This DMV let you find "why" your MS-SQL server is waiting: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 wait_type, wait_time_ms, signal_wait_time_ms , wait_time_ms - signal_wait_time_ms AS RealWait , CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER()) AS [% Waiting] , CONVERT(DECIMAL(12,2), (wait_time_ms - signal_wait_time_ms) * 100.0 / SUM(wait_time_ms) OVER()) AS [% RealWait] FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE '%SLEEP%' AND wait_type != 'WAITFOR' ORDER BY wait_time_ms DESC
2 People are following this question.
