Skip to main content

How to Identify Active Transactions Filling the TempDB

  • "Our application is not functioning, and we are receiving reports that the TempDB is full. What is filling up the TempDB?"

Question​

How can you identify which transactions are active and clogging the TempDB?

Answer​

The following statements will help to identify which transactions are active and filling the TempDB. Analysis of these results will help to isolate which transactions are at fault.

NOTE: The data in TempDB is retained until the restart of the server. If you are in critical need of space after running the below queries, please restart your SQL Server as a temporary fix.

  • Determines Size and Usage of Transaction Log:
DBCC SQLPERF(LOGSPACE);
  • Checks for Active Transactions:
SELECT 
    database_id,
    DB_NAME(database_id) AS DatabaseName,
    log_reuse_wait_desc
FROM 
    sys.databases;
  • Finds Open Transactions:
DBCC OPENTRAN;
  • Checks for Uncommitted Transactions
SELECT 
    transaction_id,
    transaction_state,
    name AS TransactionName,
    transaction_begin_time
FROM 
    sys.dm_tran_active_transactions;