CODEWITHSUNDEEP
c#sql-server-2008
dragullar
dragullar

Reputation: 355

Stored procedure returns "Timeout expired"

In my Windows application, I use SQL Server 2008. My database size is 5086080 KB. Now I get the error as timeout expired when saving a transaction by using a stored procedure. So I set command timeout to 1200. It works fine. But I think it shouldn't because insert data have 2 or 3 lines. Is there any other way to solve this problem?

This is detail error message:

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

Upvotes: 0

Views: 1709

Answers (3)

Lee Willis
Lee Willis

Reputation: 1582

Is somebody else keeping a transaction open that is holding up your query? Run sp_who or sp_who2 on the server to see what else is running.

Upvotes: 0

Vikram Bose
Vikram Bose

Reputation: 3365

Timeout is entirely on how long the actual SQL command is likely to take.

For example example, most of our commands run sproc's that should take no longer than 30 seconds to complete, however there are a couple that run for much longer, which means they have their own high valued timeouts.

You'll need to profile how long on average your routine takes then adjust the timeout accordingly, and remember to leave room for variables like latency etc

Upvotes: 1

Ehsan
Ehsan

Reputation: 32661

you need to profile your sql query and your code at every step. only then will you be able to know the exact bottleneck in your program.

Upvotes: 0

Related Questions