Monitoring TempDB Space Usage On SQL Server

  • Anu V
  • Anu V
  • Updated date Feb 14, 2017
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.  
  1. SELECT SUM(unallocated_extent_page_count) AS [free_pages]  
  2. ,(SUM(unallocated_extent_page_count) * 1.0 / 128) AS [free_space_MB]  
  3. ,SUM(version_store_reserved_page_count) AS [version_pages_used]  
  4. ,(SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version_space_MB]  
  5. ,SUM(internal_object_reserved_page_count) AS [internal_object_pages_used]  
  6. ,(SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal_object_space_MB]  
  7. ,SUM(user_object_reserved_page_count) AS [user object pages used]  
  8. ,(SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user_object_space_MB]  
  9. FROM sys.dm_db_file_space_usage;  
  10. GO  
The query showed above says that all of them have zero except two columns. 
  1. Free_pages free_space_MB  
  2. -------------------- -----------------------  
  3. 24 0.187500  
  1. SELECT R1.session_id, R1.request_id, R1.Task_request_internal_objects_alloc_page_count, R1.Task_request_internal_objects_dealloc_page_count,  
  2. R1.Task_request_user_objects_alloc_page_count,R1.Task_request_user_objects_dealloc_page_count,R3.Session_request_internal_objects_alloc_page_count ,  
  3. R3.Session_request_internal_objects_dealloc_page_count,R3.Session_request_user_objects_alloc_page_count,R3.Session_request_user_objects_dealloc_page_count,  
  4. R2.sql_handle, RL2.text as SQLText, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM (SELECT session_id, request_id,   
  5. SUM(internal_objects_alloc_page_count) AS Task_request_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count)AS  
  6. Task_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Task_request_user_objects_alloc_page_count,  
  7. SUM(user_objects_dealloc_page_count)AS Task_request_user_objects_dealloc_page_count FROM sys.dm_db_task_space_usage   
  8. 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,  
  9. 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,  
  10. SUM(user_objects_dealloc_page_count)AS Session_request_user_objects_dealloc_page_count FROM sys.dm_db_Session_space_usage   
  11. GROUP BY session_id) R3 on R1.session_id = R3.session_id   
  12. left outer JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id  
  13. OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS RL2  
  14. Where  
  15. Task_request_internal_objects_alloc_page_count >0 or  
  16. Task_request_internal_objects_dealloc_page_count>0 or  
  17. Task_request_user_objects_alloc_page_count >0 or  
  18. Task_request_user_objects_dealloc_page_count >0 or  
  19. Session_request_internal_objects_alloc_page_count >0 or  
  20. Session_request_internal_objects_dealloc_page_count >0 or  
  21. Session_request_user_objects_alloc_page_count >0 or  
  22. 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. 
  1. SELECT database_transaction_log_bytes_reserved,session_id   
  2.   FROM sys.dm_tran_database_transactions AS tdt   
  3.   INNER JOIN sys.dm_tran_session_transactions AS tst   
  4.   ON tdt.transaction_id = tst.transaction_id   
  5.   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. 
  1. SELECT *  
  2. FROM sys.sysprocesses  
Here was the query which was the cause. 
  1. SELECT *  
  2. INTO #company  
  3. FROM openquery(ADSI, N'SELECT  company     
  4.      FROM ''LDAP://DC02.domain.corp''  
  5.                 WHERE objectCategory = ''Person'' and company=''Foo'' and l=''bar''')  
  6.    
  7. SELECT DISTINCT *  
  8. FROM #company  
  9. WHERE company IS NOT NULL  
  10.    
  11. 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.