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.



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;

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,
R3.Session_request_internal_objects_alloc_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
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.

FROM sys.sysprocesses

Here was the query which was the cause.

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

DROP TABLE #company



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


About the Author

Pinal Dave

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Start the discussion at forums.toadworld.com