May 6, 2013 at 3:48 am
Hi,
I had make changes in server connection properties and set timeout time 300 Secs.
Now i had connect to a database (say abcd) through query window ran one query on that and see number of session through sp_who2 in new query windows (master) and i got 3 session for that database(abcd).
now i closed the query window(abcd) and i still got 2 session in sleeping state.
Can anyone explain why it still shows in session, I am from Oracle background and as per my knowledge all session should be killed if i disconnect for that session.
May 6, 2013 at 4:08 am
Hi,
The timeout is not for the amount of time a client is connected.
The remote query timeout kills a running query if the timeout is passed.
If the query is finished on time, the session will not be killed.
May 6, 2013 at 4:30 am
Hi,
Thanks for responce.
Is there any settings in sql server to set timeout for ideal process.
and i have one more doubt i had run single query on one table why it generate 3 session for that.
May 6, 2013 at 4:36 am
Timeout is a client concept, not one the server deals with. It's the client that decides that a query has been running too long and the client that tells SQL Server to stop the query.
The timeout you can set in SQL is the remote query timeout, for when this SQL Server is acting as a client and calling another server.
As for multiple sessions, object explorer has its own session, the intellisense does iirc, each query window has its own session.
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
May 6, 2013 at 4:38 am
I'm not sure what you mean when you say "ideal", but the default is 600 and most of the times it is left that way.
About the 3 sessions, do they have the same spid?
May 6, 2013 at 4:44 am
Thanks for the response, now I understand the timeout concept, but can u explain me how sql server kill sleeped session is there any setting available to kill them in short time.
And in second part of my Q. i had open one query window(abcd database) run single command and now from master database i got 3 session. after closing query window now from master database i got 2 session but thr should not be any session.
May 6, 2013 at 4:45 am
Matan_Yungman (5/6/2013)
I'm not sure what you mean when you say "ideal", but the default is 600 and most of the times it is left that way.About the 3 sessions, do they have the same spid?
i mean by Ideal session is: after execution of query, session not in use and is in sleeping state.
it should be killed by sql server after any specific time slice(as killed in Oracle)
No there are 3 different SPID.
May 6, 2013 at 6:45 am
suneet.mlvy (5/6/2013)
Thanks for the response, now I understand the timeout concept, but can u explain me how sql server kill sleeped session is there any setting available to kill them in short time.
SQL doesn't kill sleeping connections, there is no setting available to have it kill them and to be honest there's seldom any reason to kill sleeping connections, they're not running so they're not using CPU.
And in second part of my Q. i had open one query window(abcd database) run single command and now from master database i got 3 session. after closing query window now from master database i got 2 session but thr should not be any session.
As I said, there will be separate connections from object explorer, from intellisense, etc. Closing the query window will close the session that it opened, but not the others
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
May 7, 2013 at 4:26 am
GilaMonster (5/6/2013)
suneet.mlvy (5/6/2013)
Thanks for the response, now I understand the timeout concept, but can u explain me how sql server kill sleeped session is there any setting available to kill them in short time.SQL doesn't kill sleeping connections, there is no setting available to have it kill them and to be honest there's seldom any reason to kill sleeping connections, they're not running so they're not using CPU.
And in second part of my Q. i had open one query window(abcd database) run single command and now from master database i got 3 session. after closing query window now from master database i got 2 session but thr should not be any session.
As I said, there will be separate connections from object explorer, from intellisense, etc. Closing the query window will close the session that it opened, but not the others
Thanks Gila 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply