x

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?

more ▼

asked Dec 20, 2011 at 04:13 AM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 33 39 44

Have you checked what waits show up in SQL Server? (sys.dm_os_wait_stats)
Dec 20, 2011 at 08:27 AM Shawn_Melton
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.
Sep 27, 2012 at 07:57 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Dec 21, 2011 at 02:27 PM

Martin Schoombee gravatar image

Martin Schoombee
221 2

(comments are locked)
10|1200 characters needed characters left

[Troubleshooting: Timeout Expired][1]

[1]: http://technet.microsoft.com/en-us/library/ms190181.aspx
more ▼

answered Dec 24, 2011 at 09:38 AM

salum gravatar image

salum
213 1

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Sep 26, 2012 at 09:28 PM

sql-lover gravatar image

sql-lover
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x242
x21
x18
x9

asked: Dec 20, 2011 at 04:13 AM

Seen: 3197 times

Last Updated: Sep 27, 2012 at 07:57 AM