Monitoring TempDB Space Usage On SQL Server

In this blog, you will learn about monitoring TempDB space usage.

The database file and the log file for the Tempdb has grown to a huge size. I found Tempdb data file (MDF) was just 5 GB but the transaction log file (LDF) was 80 GB. 

What I have done?

First, I ran the query given below 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

The query showed above says that all of them have 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. Thus, I ran a 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 the script given below. 

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

Mostly, there could have been something that went wrong, while running the openquery. Now, there are two ways to come out of the situation, which are given below.

1. Kill above SPID's and shrink the database files.
2. Restart the instance of SQL Server.