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
,	'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
	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
	db_name(sddssu.database_id) in ('tempdb')
	and	sddssu.user_objects_alloc_page_count > 0

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s