THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

TempDB Space Usage

Aaron Bertrand, a blogger on and keeper of is one of those SQL Server MVPs whose material is always on my must-read list.  Here's a really useful query he developed to determine the space used by objects in TempDB:


 SPID = s.session_id,





 granted_memory = CONVERT(INT, r.granted_query_memory*8.00),


 sourcedb = DB_NAME(r.database_id),

 workdb = DB_NAME(dt.database_id), 



FROM sys.dm_exec_sessions s

INNER JOIN sys.dm_db_session_space_usage su

   ON s.session_id = su.session_id

   AND su.database_id = DB_ID('tempdb')

INNER JOIN sys.dm_exec_connections c

   ON s.session_id = c.most_recent_session_id

LEFT OUTER JOIN sys.dm_exec_requests r

   ON r.session_id = s.session_id





   FROM sys.dm_tran_session_transactions t

   INNER JOIN sys.dm_tran_database_transactions dt

      ON t.transaction_id = dt.transaction_id 

   WHERE dt.database_id = DB_ID('tempdb')

   GROUP BY  session_id,  database_id

   ) dt

   ON s.session_id = dt.session_id

 CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,

 c.most_recent_sql_handle)) t

 LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg

   ON s.session_id = mg.session_id

 WHERE (r.database_id = DB_ID('tempdb')

   OR dt.database_id = DB_ID('tempdb'))

  AND s.status = 'running'



You might consider writing your own query based upon dm_db_session_space_usage but this works quite well.  Thanks for sharing this, Aaron!






Published Tuesday, October 23, 2007 7:59 AM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AaronBertrand said:

Thanks Kevin, appreciate the kind words!

October 28, 2007 10:30 PM

David Hunter said:

Do you guys have a SQL2005 version too?

This line...

CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, no good in 2005.

October 31, 2007 9:31 PM

cinahcaM madA said:

David: You mean SQL Server 2000?  There is no equivalent to CROSS APPLY in 2000.  That line will work fine in 2005, though.

November 5, 2007 2:24 PM

Kalen Delaney said:

This post really should have a topic of "Pet Peeve" but I really didn't want to sound quite so negative.

December 5, 2007 12:44 AM

Kalen Delaney said:

This post really should have a topic of "Pet Peeve" but I really didn't want to sound quite so negative.

December 8, 2007 7:10 PM

Bill said:

Do you have a version that works with SQL 2000?

Thank you,


February 6, 2008 3:16 AM

KKline said:

For those of you looking to do the same thing in TempDB, you refer to this (again, tanx to Aaron Bertrand):


February 26, 2008 5:10 PM

gopi said:

can you provide same in powershell script

December 22, 2014 4:20 AM

KKline said:

Sorry, Gopi. I'm not a PowerShell guy.

December 29, 2014 6:58 PM

Leave a Comment


About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS,

This Blog



Privacy Statement