Find TEMPDB Pest - Which SPID Is Taking Up All The Space?

  • So I had an issue the other morning where the TEMPDB data file on an instance was about to fill. Before I could find the offending SPID, I needed to cycle SQL Server, or it was going down anyways. I have a number of tools in my arsenal for finding such SPIDS and it bugs me that I could not. So, a little practice. Please review/comment on my code as you see fit.

    In my current environment I have code that tells me 11.56GB of TEMPDB space is being utilized. I can confirm with the following, that this 11GB is being used by the Version Store. Snapshot isolation is in use FYI

    SELECT DB_NAME(database_id) AS [DBName]

    ,SUM(user_object_reserved_page_count) * 8 / 1024 / 1024 AS [Usr_Objects_GB]

    ,SUM(internal_object_reserved_page_count) * 8 / 1024 / 1024 AS [Int_Objects_GB]

    ,SUM(version_store_reserved_page_count) * 8 / 1024 / 1024 AS [VStore_GB]

    ,SUM(mixed_extent_page_count) * 8 / 1024 / 1024 AS [MixedExtent_GB]

    FROM sys.dm_db_file_space_usage

    GROUP BY database_id

    So, now I want to know which SPID is using the majority of that space. Version store space is categorized as Internal (versus User) within TEMPDB, so I would run something like the following. My goal is to find the SPID with a "large" discrepancy between allocated and deallocated. However, I see no such discrepancy.

    So, WHO has the 11GB of TEMPDB space. I must be missing something "obvious"

    SELECT

    s.host_name

    ,su.[session_id]

    ,d.name [DBName]

    ,su.[database_id]

    ,su.[internal_objects_alloc_page_count] [Int_Pg_Alloc]

    ,su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc]

    ,su.[internal_objects_alloc_page_count] - su.[internal_objects_dealloc_page_count] [Int_AllocDiff]

    ,( su.[internal_objects_alloc_page_count] * 1.0 / 128 )/1024 [Int_Alloc_GB]

    ,( su.[internal_objects_dealloc_page_count] * 1.0 / 128 )/1024 [Int_DeAlloc_GB]

    ,(( su.[internal_objects_alloc_page_count] * 1.0 / 128 )/1024)-(( su.[internal_objects_dealloc_page_count] * 1.0 / 128 )/1024) Int_AllocDiff_GB

    FROM [sys].[dm_db_session_space_usage] su

    INNER JOIN sys.databases d ON su.database_id = d.database_id

    INNER JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id

    WHERE ( su.user_objects_alloc_page_count > 0

    OR su.internal_objects_alloc_page_count > 0

    )

    ORDER BY Int_AllocDiff_GB DESC

  • I believe (I'm sure someone will correct me if I'm wrong) that version store info is separate to internal objects info (which are more to do with large hash joins etc)

    The following query from this blog post can track long running transactions that affect version store.

    SELECT qt.TEXT query_name,

    dm_tran_active_snapshot_database_transactions.transaction_id,

    transaction_sequence_num,

    elapsed_time_seconds

    FROM sys.dm_tran_active_snapshot_database_transactions,

    sys.dm_tran_session_transactions,

    sys.dm_exec_requests

    OUTER APPLY sys.Dm_exec_sql_text(sql_handle) qt

    WHERE sys.dm_tran_active_snapshot_database_transactions.transaction_id = sys.dm_tran_session_transactions.transaction_id

    AND sys.dm_exec_requests.session_id = sys.dm_tran_session_transactions.session_id

    ORDER BY elapsed_time_seconds DESC

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • This script could be a starting point:

    http://www.sqlservercentral.com/scripts/tempdb/72007/

    -- Gianluca Sartori

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply