Is possible to know who is using tempdb?

Question

Is possible to know who is using tempdb?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
April 7, 2011 at 10:46 am

#248505

Hello all
I want to check how many space in tempdb is using each individual spid, is that possible?
Thanks in advance

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

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

Eddie Wuerch SSChampion Points: 12504 More actions · Answer 1

You can get that info with sys.dm_db_session_space_usage and, at a more granular level, sys.dm_db_task_space_usage.

Pages are 8kB each, so multiply the alloc_page_count and dealloc_page_count values by 8 to get the number of bytes used by each session or task.

You can the session_id column from those views back to sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_requests to find out the login/machine, current execution info, etc.

-Eddie

Eddie Wuerch
MCM: SQL

Lowell SSC Guru Points: 323482 More actions · Answer 2

Eddie thanks for the pointers...I'm not getting the results I'm expecting, though.

I created a temp table with the typical SELECT * into #tmp fromSomeTable.

then i ran this command to see what my spid is using...can you point out the error in my calculation?

/*--Results

LoginName host_name program_name client_interface_name client_net_address KBytesUsed

lowell DEV223 Microsoft SQL Server Management Studio - Query .Net SqlClient Data Provider 192.168.0.55 0

*/

SELECT

COALESCE(sess.nt_user_name,sess.login_name) AS LoginName,

sess.host_name,

sess.program_name,

sess.client_interface_name,

conns.client_net_address,

memUsed.KBytesUsed

FROM sys.dm_exec_sessions sess

LEFT OUTER JOIN sys.dm_exec_connections conns

ON sess.session_id = conns.session_id

LEFT OUTER JOIN

(SELECT

session_id,

SUM(user_objects_alloc_page_count

+ user_objects_dealloc_page_count

+ internal_objects_alloc_page_count

+ internal_objects_dealloc_page_count) * 8 AS KBytesUsed

FROM sys.dm_db_task_space_usage

WHERE database_id = 2

GROUP BY session_id

)memUsed

ON memUsed.session_id = sess.session_id

--WHERE memUsed.KBytesUsed > 0

WHERE sess.session_id = @@SPID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323482 More actions · Answer 3

in answer to my own question, i think i needed to use dm_db_session_space_usage instead; that gives me 600 KBbytesUsed in my example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jorgepc SSC Enthusiast Points: 103 More actions · Answer 4

Correct, the [font="Courier New"]sys.dm_db_session_space_usage[/font] will include the session information you are looking for.

Example:

SELECT

session_id,

(SUM(user_objects_alloc_page_count)*1.0/128) AS ,

(SUM(internal_objects_alloc_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_session_space_usage

GROUP BY session_id

ORDER BY session_id;

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 5

ricardo_chicas

SSCertifiable

Points: 5024

April 7, 2011 at 1:03 pm

#1308782

Thank you so much!!

This is perfect!