Interesting program times out

Question

Interesting program times out

TRACEY-320982

SSChampion

Points: 13653
More actions
July 2, 2008 at 5:07 pm

#193411

On the server it is set up to remote query timeout to 600 seconds on the server 2005 properites page.
One developer says his program keeps timing out so i wrote something to test with.
and yes it definately does time out.
period = "00:00:30" works
period = "00:01:00" definately says time out.
I can run this in SQL the sp and it works just fine.
Any suggestions as to where this is timing out on the IIS or the Driver.
Using Provider=SQLOLEDB
-----
This is the test program.
create PROCEDURE TESTTIME
@VARIABLE_TIME nvarchar(20)
AS
declare @START_TIME DATETIME, @END_TIME DATETIME
SET @START_TIME = GETDATE()
--WAIT
SET @END_TIME = getdate()
--SET @VARIABLE_TIME = '00:01:35'
WAITFOR DELAY @VARIABLE_TIME
SELECT @END_TIME = GETDATE()
SELECT @START_TIME AS 'START TIME', @END_TIME AS 'END TIME'
Web Page just to test with.


<%
Dim dbConn
Dim rs
Dim sql
'---Open connection
set dbConn = server.createobject("adodb.connection")
DBCONN.OPEN "add connection string her for server"
'---set recordset
set rs = server.CreateObject("adodb.recordset")
DIM period
period = "00:01:00"
sql = "TESTTIME '" & period & "'"
' Execute the statement
set rs = dbConn.Execute(sql)%>
<% ' Write out all the elements requested in the
' sql statement as table headers
for each element in rs.fields%>
<% next
' End table headers %>
<% ' Write out all the values in the record
do while not rs.eof
for each element in rs.fields %>
&nbsp
<%
next
' end of record %>
<% ' Move to the next record
rs.movenext
' Loop to the beginning
loop%>
<%
RS.Close
dbconn.Close
end if
response.end
%>

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

this is often a problem - you might try optimising the code so it takes less than 30 seconds ???? Most likely there will be timeouts set in connection strings and IIS has a number of settings and config which can cause this. Use profiler to capture the connection string settings and look in the various IIS setting ( I'm not so good on IIS - just know it can be a problem )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

gbd77rc Ten Centuries Points: 1031 More actions · Answer 2

Hi

By default the connection timeout is set to 30 seconds, so if the connection does not have timeout=600; in it, it willl default to 30 seconds.

I normally set it in the connection string (web.config connectionstrings collection), but if I think a block of code/stored procedure will take awhile I will override the connection string append the timeout property when creating the connection object.

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

i put in dbConn.CommandTimeout = 0 and it worked - shame on the web developer.....

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 4

Hi and hope this helps. Set the query parameter at the SQL Server level to 0, which will all queries unlimited time to run. I agree with the rest of you to check the SQL statement to tune your query, maybe some indexes are needed? Maybe some joins needs to be tweak?

You can open a new query and select "see excution plan" before you run the query to see where could be the bottleneck if any.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

This is happening again after 10 minutes

So on server, properties, remote connectoins, remote query - set this to 0 rather than 600 default i increased to 1200 but that was not enough.

If connections come in will they still be clean up - i seen web connections come in they process then they are closed.

No restart required?

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 6

yes, tracey. The connections stay up as long as the staetement is running. After that they will be closed. You will see them a little bit longer after the statements are completed and it is just how SQL Server refreshes the connection pool. Use sp_who2 (return all the connections, active or sleeping) to check if the connections are dropped. There is a stored procedure I found here to return only the active connections (sp_what I think is the name of the script)

0 means that the query will run for as long as it is needed. Regardless of this general principle, if a query is running for that long. You might want to double check the statements. If the statments are being developed by your developers team send it back to them first to have all the players in the sme page, otherwise you might ended up being the owner of a faulty process. Better safe than sorry!!