February 20, 2012 at 8:04 am
I was monitoring the sessions through this dmv and found that sometimes even if the session is closed ,it is viewable in the result. Can someone clarify this nature ?
February 20, 2012 at 8:08 am
how do you determin that the session is closed?
are you sure its just not SQL reassigning the SPID to a different connection?
February 20, 2012 at 8:20 am
sqlnaive (2/20/2012)
I was monitoring the sessions through this dmv and found that sometimes even if the session is closed ,it is viewable in the result. Can someone clarify this nature ?
You can try it yourself. Open two query windows , run separate queries in those windows. Check that both are appearing in the sys.sysprocesses results. Now close one of them & check that it has gone out of the result.
February 21, 2012 at 2:01 am
I tried the same actually. I opened two sessions and from one of them I ran one update statement. Once I closed that session with update command, and performed select from sys.sysprocesses, I could see my processid and hostname with the sql_handle which provided me my executed update query.
February 21, 2012 at 2:18 am
sqlnaive (2/21/2012)
I tried the same actually. I opened two sessions and from one of them I ran one update statement. Once I closed that session with update command, and performed select from sys.sysprocesses, I could see my processid and hostname with the sql_handle which provided me my executed update query.
So you can see the status of your session over there. It should be sleeping/Awaiting Command.
February 21, 2012 at 6:22 am
Yes. It says sleeping. But then there are other sessions which are not closed but still seen as sleeping. How can I differ between the two ? Should these sessions be there ? Or should there be some different architecture to handle this (like storing historic session information) ?
February 21, 2012 at 6:43 am
You should not be worried about the Sleeping/Awaiting Commands session unless they are not blocking other sessions. SQL Server does it, so that it can reuse the same connection.
February 22, 2012 at 4:38 am
How long these process ids remain there (in case the sessions are closed). I want to check only those session ids which are live, in use or not in use.
February 23, 2012 at 1:47 am
How long these process ids remain there (in case the sessions are closed). Anyone ?
February 23, 2012 at 1:54 am
sqlnaive (2/22/2012)
How long these process ids remain there (in case the sessions are closed). I want to check only those session ids which are live, in use or not in use.
I would suggest to use this :
Execute sp_who2 Active
This will provide you only those session ids which are currently active on the server, not all the sessions. As much as I know many DBAs use just that instead of sys.sysprocesses.
February 23, 2012 at 2:44 pm
Ditto @Divine , use that sweet thing religiously!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 29, 2012 at 1:29 am
Agreed - The only thing is that I can use sys.sysprocesses in joins with other tables to get useful information. Else I always has that sp_who[2] option.
February 29, 2012 at 1:43 am
sqlnaive (2/29/2012)
Agreed - The only thing is that I can use sys.sysprocesses in joins with other tables to get useful information. Else I always has that sp_who[2] option.
In that case you can filter those sessions with "Sleeping/Awaiting Command" status in your WHERE clause. However if you could explain your case where you are using sys.sysprocesses in join may be we can provide better option.
February 29, 2012 at 3:42 am
select a.spid, b.text from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) b
This is the query I'm using. This is giving me even those sessions which are closed in query analyzer.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply