Timeout Period

Question

Post reply

Timeout Period

MothInTheMachine

Hall of Fame

Points: 3251
More actions
June 22, 2010 at 1:12 pm

#224127

I'm wondering what factors have to happen for a timeout to fire on SQL Server 2008. Does a thread have to be in a specific state for the amount of time that has been configured somewhere before it fires? If there is a specific state can you tell me what that is called? Just trying to get more details on this situation.
Thx
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 6 posts - 1 through 5 (of 5 total)

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

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

There are so many reasons for time out, please provide more context on that?

"More Green More Oxygen !! Plant a tree today"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SQL doesn't know what timeouts are (other than lock timeouts). Timeouts are a client error, the client issues a request to SQL and, if it has not had a response within the timeout perior, the client issues an attention event to SQL, requesting that it cancel the query. The client then returns a timeout error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 3

What effect does the Query Timeout setting in SQL Server 2008 have? Is it related in any way to client timeout periods or is this for the internal locks? Our value was set to never time out (0) and I set it to 600 (seconds..10 minutes) Is that a bad idea or should the never setting be left in place? Seems that would only hide poor performance values.

Thanks!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

MothInTheMachine (6/23/2010)
What effect does the Query Timeout setting in SQL Server 2008 have?

There isn't a query timeout setting in SQL. There's a remote query timeout, which affects queries that SQL sends to linked/remote server. It's unrelated to client timeouts which are set and fired from the client app only. There's also a lock timeout that affects how long SQL waits for locks.

The first is by default 600 sec, the second by default -1 (never timeout)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 5

Ok..that clears that up. We don't use that kind of set up so my settings were in vain.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;