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

avatar image

Mandar Alawani
376 40 43 48

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

avatar image

Martin Schoombee
611 4 4

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

answered Dec 24, 2011 at 09:38 AM

avatar image

salum
213 2 2

(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

avatar 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.

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:

x2016
x273
x36
x20
x13

asked: Dec 20, 2011 at 04:13 AM

Seen: 4698 times

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

Copyright 2016 Redgate Software. Privacy Policy