SQL SERVER – Script to Find and Monitoring TempDB Space Usage

February 2, 2017

SQL SERVER - Script to Find and Monitoring TempDB Space Usage tempdb I have started blogging on my site long ago and my whole idea was to make script repository so that others can reuse it. Now when I look back, there are many scripts and many solutions which I have written. Personally, I have used many of them. Today I am sharing another script which I have used with one of my clients and it was very useful for me in troubleshooting a strange TempDB log file issue. Let us learn about Monitoring TempDB Space Usage.

ISSUE

The database file and the Log file for the Tempdb has grown to a huge size. Found the Tempdb data file (mdf) was just 5 GB but the transaction log file (ldf) was 80 GB.

What I have done?

First, I ran below query to find out the usage.

SELECT SUM(unallocated_extent_page_count) AS [free_pages]
,(SUM(unallocated_extent_page_count) * 1.0 / 128) AS [free_space_MB]
,SUM(version_store_reserved_page_count) AS [version_pages_used]
,(SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version_space_MB]
,SUM(internal_object_reserved_page_count) AS [internal_object_pages_used]
,(SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal_object_space_MB]
,SUM(user_object_reserved_page_count) AS [user object pages used]
,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user_object_space_MB]
FROM sys.dm_db_file_space_usage;
GO

Above query showed all of them as zero except two columns.

Free_pages free_space_MB -------------------- ----------------------- 24 0.187500

SELECT R1.session_id, R1.request_id, R1.Task_request_internal_objects_alloc_page_count, R1.Task_request_internal_objects_dealloc_page_count,
R1.Task_request_user_objects_alloc_page_count,R1.Task_request_user_objects_dealloc_page_count,R3.Session_request_internal_objects_alloc_page_count ,
R3.Session_request_internal_objects_dealloc_page_count,R3.Session_request_user_objects_alloc_page_count,R3.Session_request_user_objects_dealloc_page_count,
R2.sql_handle, RL2.text as SQLText, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM (SELECT session_id, request_id, 
SUM(internal_objects_alloc_page_count) AS Task_request_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count)AS
Task_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Task_request_user_objects_alloc_page_count,
SUM(user_objects_dealloc_page_count)AS Task_request_user_objects_dealloc_page_count FROM sys.dm_db_task_space_usage 
GROUP BY session_id, request_id) R1 INNER JOIN (SELECT session_id, SUM(internal_objects_alloc_page_count) AS Session_request_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count)AS Session_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Session_request_user_objects_alloc_page_count,
SUM(user_objects_dealloc_page_count)AS Session_request_user_objects_dealloc_page_count FROM sys.dm_db_Session_space_usage 
GROUP BY session_id) R3 on R1.session_id = R3.session_id 
left outer JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS RL2
Where
Task_request_internal_objects_alloc_page_count >0 or 
Task_request_internal_objects_dealloc_page_count>0 or
Task_request_user_objects_alloc_page_count >0 or
Task_request_user_objects_dealloc_page_count >0 or
Session_request_internal_objects_alloc_page_count >0 or
Session_request_internal_objects_dealloc_page_count >0 or
Session_request_user_objects_alloc_page_count >0 or
Session_request_user_objects_dealloc_page_count >0

This showed a lot of rows and few rows were suspicious. So, I ran command to find the transaction which is still using transaction log.

SELECT database_transaction_log_bytes_reserved,session_id 
  FROM sys.dm_tran_database_transactions AS tdt 
  INNER JOIN sys.dm_tran_session_transactions AS tst 
  ON tdt.transaction_id = tst.transaction_id 
  WHERE database_id = 2;

This also showed the same rows which were suspicious. Finally, I wanted to know the source of those queries and ran following script.

SELECT *
FROM sys.sysprocesses

Here was the query which was the cause.

SELECT *
INTO #company
FROM openquery(ADSI, N'SELECT  company   
     FROM ''LDAP://DC02.domain.corp''
                WHERE objectCategory = ''Person'' and company=''Foo'' and l=''bar''')

SELECT DISTINCT *
FROM #company
WHERE company IS NOT NULL

DROP TABLE #company