Agent Jobs

Find What Process Is Taking Up Space In TempDB

Here’s a script I threw together to find what process is taking up space in the TempDB, and where the process came from.

-- find what objects are taking up the most TempDB space and where the process is coming from.
use master;
set nocount on
select 
	sddssu.session_id
,	'login name'		= sdes.login_name
,	'from device'		= sdes.host_name
,	'database'		= db_name(sddssu.database_id)
,	'space consumed in gb'	= (sddssu.user_objects_alloc_page_count *8 / 1024 / 2014)
,	'process'		= sder.command
,	'statement'		= sdest.text
from	
	sys.dm_db_session_space_usage sddssu 
	join sys.dm_exec_requests sder on sddssu.session_id = sder.session_id
	cross apply sys.dm_exec_sql_text(sder.sql_handle) sdest
	join sys.dm_exec_sessions sdes on sdes.session_id	= sder.session_id
where	
	db_name(sddssu.database_id) in ('tempdb')
	and	sddssu.user_objects_alloc_page_count > 0
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s