SQL SERVER – Script to Find and Monitoring TempDB Space Usage

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

CONCLUSION

Mostly there could have been something that went wrong while running the openquery.  Now there were two ways to come out of the situation would be:

1. Kill above SPIDs and shrink the database files.
2. Restart the instance of SQL Server.

Do you have any such scripts which you would like to share with other readers?

Reference: Pinal Dave (https://blog.sqlauthority.com)

First appeared on SQL SERVER – Script to Find and Monitoring TempDB Space Usage