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; GO
Above query showed all of them as zero except two columns.
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
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:
- Kill above SPIDs and shrink the database files.
- 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)