How to adjust timeout limit for stored procedures?

[jjjkkk77]

Hi dear all,

I have a long-running stored procedure that sometimes runs over 1 hour and gets aborted by sql server with the following error

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I think by default sql server let stored procedures run for 1 hour at maximum. How to set the timeout longer? Thank you

 

[gmmastros]

As far as I know, there is no time-out setting in sql server.

Instead, most time-outs are specified in front end application. Of course, you can consider Query Analyzer and SQL Server Management Studio as front end applications for SQL Server.

I don't have SSMS on this computer, but if you are using Query Analyzer, you can configure the time-out settings with...

Click Tools -> Options
Click Connections tab
You will see 'Query Time Out (Seconds)'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

ANd if you truly havea stored proc running that long, you need to look at ways to optimize it not look at how to increase the time out. Check out the excution plan, is it using any indexes? Do you have a cursor that you can replace with a significantly more efficient piece of set-based code. Is there some other way to do the task that will take less time? There are many many way s to imporve performance.

"NOTHING is more important in a database than integrity." ESquared