Active Sessions

Categories:

SQL Server

List of currently active sessions for each database on your servers.


Please note that you need VIEW SERVER STATE permission to view all the sessions. Otherwise, you’ll see only your own.


--- Currently Active Sessions
Select
s.spid as 'pid'
,s.blocked as 'Blocks'
,RTRIM(CAST(s.hostname as nvarchar(256))) as 'FromServer'
,RTRIM(CAST(s.loginame as nvarchar(256))) as 'UserName'
,RTRIM(CAST(d.name as nvarchar(256))) as 'DatabaseName'
,RTRIM(REPLACE(s.lastwaittype,'RESERVED_MEMORY_ALLOCATION_EXT  ','RES_MEM_ALLOC_EXT')) as 'WaitType'
,s.waittime / 1000 as 'WaitTime' -- wait time in seconds
,RTRIM(CAST(s.cmd as nvarchar(32))) as 'Statement'
,s.status as 'Status'
,c.num_reads as 'PageReads' -- number of packet reads
,c.num_writes as 'PageWrites' -- number of packet writes
,RTRIM(CAST(s.program_name as nvarchar(256))) as 'ProgramName'
,t.text as 'ExecutedQuery'
,datediff(minute,s.last_batch, GETDATE()) as 'Duration' -- execution time in munites
--,EQP.query_plan as 'execution plan'
-- r.percent_complete
--into #tmp
from sys.sysprocesses s
join sys.databases d on d.database_id = s.dbid
left join sys.dm_exec_requests r on r.session_id = s.spid
cross apply sys.dm_exec_sql_text (s.sql_handle) t
--CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS EQP
inner join sys.dm_exec_connections c
on c.session_id = s.spid
where (s.cmd not like '%AWAITING%' or s.status = 'suspended')
and t.text not like '%--- Currently Active Sessions Select%'
order by duration desc

Tags:

No Tag

80 Responses

  1. Great post. I was checking constantly this blog and I’m impressed!Very useful information specially the last part 🙂 I care for such information a lot.I was looking for this certain information for a very long time.Thank you and best of luck.

  2. It’s an amazing paragraph designed for all the online visitors; they willobtain advantage from it I am sure.

  5. Good day! This post could not be written any better!Reading this post reminds me of my good old room mate! Healways kept chatting about this. I will forward this write-up tohim. Fairly certain he will have a good read. Thanks for sharing!

  9. Hi my loved one! I wish to say that this article is awesome, nice written and come with approximately all significant infos. I’d like to look extra posts like this.

  21. Usually I do not learn article on blogs, however I wish to say that this write-up very pressured me to take a look at and do it! Your writing taste has been surprised me. Thanks, very great article.

  22. Thanks for another magnificent article. Where else could anybody get that kind of info in such a perfect way of writing? I have a presentation next week, and I’m on the look for such info.

  23. I do trust all the concepts you have introduced in your post. They are really convincing and will certainly work. Nonetheless, the posts are too brief for newbies. Could you please extend them a bit from next time? Thank you for the post.

  25. Amazing! This blog looks exactly like my old one! It’s on a completely different subject but it has pretty much the same layout and design. Excellent choice of colors!

  26. Very neat blog post.Really looking forward to read more. Cool.Loading…

  30. A fascinating discussion is definitely worth comment.I do think that you should publish more about this subject, it mightnot be a taboo subject but generally folks don’t speak aboutthese issues. To the next! All the best!!

  32. When I initially commented I clicked the “Notify me when new comments are added” checkbox andnow each time a comment is added I get several e-mails with the same comment.Is there any way you can remove people from that service?Thanks a lot!

  35. Thanks for the marvelous posting! I seriously enjoyed readingit, you will be a great author.I will be sure to bookmark your blog anddefinitely will come back down the road. I want to encourage you continue your great writing,have a nice weekend!

  37. Hi, I do think this is an excellent blog. I stumbledupon it 😉 I am going to revisit once again since i have saved as a favorite it. Money and freedom is the greatest way to change, may you be rich and continue to guide other people.

  39. I need to to thank you for this very good read!! I definitely enjoyed every bit of it. I’ve got you book marked to check out new things you postÖ

  40. It’s hard to find knowledgeable people for this subject, however, you sound like you know what you’re talking about! Thanks

  47. Seriously a lot of wonderful advice.admission essay editing services cpm homework essay writing services reviews

  66. I’d need to check with you here. Which isn’t one thing I normally do! I get pleasure from studying a publish that will make people think. Additionally, thanks for allowing me to comment!

  68. Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I will come back yet again since I saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to help other people.

  71. Thanks for the update, is there any way I can receive an alert email every time there is a new article?

  73. Hi there! Would you mind if I share your blog with my twitter group?There’s a lot of folks that I think would really enjoy your content.Please let me know. Thank youLook into my blog post: One Shot Max Keto Reviews (haojiafu.net)

  75. Hi there, just became aware of your blog through Google, and found that it is truly informative.I’m gonna watch out for brussels. I’ll be grateful if you continue this in future.Lots of people will be benefited from your writing.Cheers!

  76. Hi there! Do you know if they make any plugins to assist with SEO? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success. If you know of any please share. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *