TempDB Is Full or Still Growing: Find What Is Using It, Right Now

Inserts are failing, a report has died or the drive alert has gone off, and the file that grew is tempdb. Find the session holding the space first, then which of the 3 kinds of space it holds: get the second wrong and you kill a session that held none of it, or shrink a file that has nothing to give back.

Msg 1105: Could Not Allocate Space in TempDB

Two messages mean the tempdb data files have no room left. Both are severity 17 and both are written to the error log. This is the one people search for, exactly as the server stores it:

That is the row for message_id 1105 in sys.messages on SQL Server 2025 CU8, where each %.*ls marks a value the server fills in. In a tempdb incident the database reads tempdb and the filegroup PRIMARY, and the object is usually a worktable the engine named for itself rather than anything in your schema. Documented, not reproduced here: filling tempdb on purpose means capping its files, and that is not a thing to do on an instance anyone else is using.

The sibling is 1101, which opens “Could not allocate a new page for database” and names no object: same cause, a different allocation path. On SQL Server 2025, 1138 opens the same way but ends “because that would exceed the limit set for workload group”, which means a tempdb space limit in resource governor stopped one workload, not that tempdb is full. If the database in your message is one of your own, Could Not Allocate Space Because the Filegroup Is Full (Error 1105) is the page for that case.

Plenty of these arrive with no message at all, as a disk alert or a drive that went from a third full to full inside an hour. Nothing below needs an error number, and if yours is 9002, the log section is the one to read.

The One Query: Which Session Is Using TempDB Right Now

Run this from any database on the instance. One row per session holding tempdb space, with the statement it is running, or the last one it ran if it is idle:

WITH tempdb_users AS
(
    SELECT  s.session_id,
            ISNULL(r.status, es.status)                                                  AS status,
            ISNULL(r.wait_type, '-')                                                    AS wait_type,
            (s.user_objects_alloc_page_count
             - s.user_objects_dealloc_page_count) / 128                                 AS sess_user_mb,
            (s.internal_objects_alloc_page_count
             - s.internal_objects_dealloc_page_count) / 128                              AS sess_internal_mb,
            ISNULL(tk.task_user_mb, 0)                                                   AS task_user_mb,
            ISNULL(tk.task_internal_mb, 0)                                               AS task_internal_mb,
            ISNULL(es.program_name, '')                                                  AS program_name,
            LEFT(REPLACE(REPLACE(ISNULL(st.text, ''), CHAR(13), ' '), CHAR(10), ' '), 62) AS last_statement
    FROM    sys.dm_db_session_space_usage AS s
    LEFT JOIN sys.dm_exec_sessions AS es    ON es.session_id = s.session_id
    LEFT JOIN sys.dm_exec_requests AS r     ON r.session_id  = s.session_id
    LEFT JOIN sys.dm_exec_connections AS c  ON c.session_id  = s.session_id
                                           AND c.net_transport <> 'Session'
    OUTER APPLY (
            SELECT  SUM(t.user_objects_alloc_page_count
                        - t.user_objects_dealloc_page_count) / 128     AS task_user_mb,
                    SUM(t.internal_objects_alloc_page_count
                        - t.internal_objects_dealloc_page_count) / 128 AS task_internal_mb
            FROM    sys.dm_db_task_space_usage AS t
            WHERE   t.session_id = s.session_id
    ) AS tk
    OUTER APPLY sys.dm_exec_sql_text(ISNULL(r.sql_handle, c.most_recent_sql_handle)) AS st
    WHERE   s.session_id > 50
      AND   s.session_id <> @@SPID
)
SELECT  session_id, status, wait_type,
        sess_user_mb, sess_internal_mb, task_user_mb, task_internal_mb,
        program_name, last_statement
FROM    tempdb_users
WHERE   sess_user_mb + sess_internal_mb + task_user_mb + task_internal_mb > 0
ORDER BY sess_user_mb + sess_internal_mb + task_user_mb + task_internal_mb DESC, session_id;

Page counts divided by 128 give megabytes. On the lab instance, with one idle session holding a temp table inside an open transaction and another holding a static cursor open:

That is the answer: a session, a statement and a size. Session 67 is sleeping, its batch long finished, and still holds 26 MB of #order_stage, because a temp table lives until the scope or the session that made it ends. Session 58 holds 12 MB of worktable nobody asked for by name, because a static cursor copies its whole result set into tempdb. The status and statement columns fall back to the session and its last batch on purpose: an idle session has no row in sys.dm_exec_requests, so a query that reads only that view shows NULL and a blank statement for exactly the session you are looking for. The space tells you which way to go:

• The space is in a user column. Temp tables and table variables. They live as long as the scope that created them, so until that batch or procedure ends, or the connection closes, the space is not coming back on its own.
• The space is in an internal column. The engine’s own scratch space: a sort or hash that spilled, or a cursor worktable like session 58’s. A spill means the query was granted less memory than it needed, so it is a plan and statistics problem wearing a space problem as a costume.
• Nothing much on any session, but tempdb is still full. Then the space is in the version store, which belongs to no session and appears in none of these columns, and the next section is where you find it.

Read Both Pairs of Columns, Because They Count Different Things

Session 67 reports its space under sess and session 58 under task. sys.dm_db_task_space_usage counts pages held by tasks that are still running. sys.dm_db_session_space_usage counts what a session’s completed tasks allocated and have not given back, and MS Docs notes its counters update only when a task ends. Query both, add them, and never clear a session on one pair alone. A parallel query has a row per task, which is why the query sums them. If you would rather not paste a 30 line query while the phone is ringing, Get TempDB Usage and File Balance is the packaged version of this check.

User Objects, Internal Objects, or the Version Store

This is the one check that decides what you do next, and it is a single statement against every tempdb file:

SELECT  SUM(total_page_count)                    / 128 AS total_mb,
        SUM(unallocated_extent_page_count)       / 128 AS free_mb,
        SUM(user_object_reserved_page_count)     / 128 AS user_objects_mb,
        SUM(internal_object_reserved_page_count) / 128 AS internal_objects_mb,
        SUM(version_store_reserved_page_count)   / 128 AS version_store_mb
FROM    tempdb.sys.dm_db_file_space_usage;

The same moment as the capture above, with all 3 kinds in play at once:

Whichever number is largest is the incident you are actually having, and they are not variations on a theme. They have different owners and different cures:

Drop the SUM and the same DMV returns one row per file. Here that was 8 files of 72 MB, each carrying 11 MB of version store with 52 MB to 55 MB free. SQL Server writes to each file in proportion to its free space, which MS Docs describes under file and filegroup fill strategy, so the files fill together and 1105 comes when all of them are full. A lab filegroup of one 4 MB file and one 16 MB file, both capped, raised 1105 with 0 MB free in each. What an uneven set does cost you is contention, because the largest file takes the most allocations. Get TempDB Usage and File Balance does the per-file read and the evenness check in one go, and sys.dm_db_file_space_usage on MS Docs documents every column.

Msg 9002: The Log for TempDB Is Full

This one is the tempdb log, not the data files, and the checks above will not find it. The message, as sys.messages stores it:

TempDB runs in SIMPLE recovery, so its log only fills when something stops it truncating, and in tempdb that is nearly always a transaction left open. Read the reason, then find the oldest transaction that has written to the tempdb log:

SELECT  name, log_reuse_wait_desc
FROM    sys.databases
WHERE   name = N'tempdb';

SELECT  st.session_id,
        ISNULL(es.status, '')                                   AS session_status,
        ISNULL(es.program_name, '')                             AS program_name,
        DATEDIFF(SECOND, dt.database_transaction_begin_time, GETDATE()) AS open_seconds,
        dt.database_transaction_log_bytes_used / 1024           AS log_kb_used
FROM    sys.dm_tran_database_transactions AS dt
JOIN    sys.dm_tran_session_transactions  AS st ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions            AS es ON es.session_id    = st.session_id
WHERE   dt.database_id = 2
ORDER BY dt.database_transaction_begin_time;

The reason is as of the last checkpoint. On the lab instance, with an idle session holding a transaction open in tempdb, the first statement read ACTIVE_TRANSACTION after a checkpoint and the second named the session:

End that transaction, by commit, rollback or KILL, and the log can truncate at the next checkpoint. Growing the log file buys time if the drive has room, with the same catch as growing a data file below: the size you set becomes the size tempdb starts at. Troubleshoot a full transaction log on MS Docs covers 9002 in general, and SQL Server Transaction Log Full is the page for the same message on a user database.

What You Can Do Right Now, Without a Restart

In order of how much it costs other people. Nothing here needs the service stopped, and the restart people reach for first is the most expensive option on the list.

Grow a File, or Add One, If the Drive Has Room

If free_mb is near zero but the drive is not, the files are capped or autogrowth is off. ALTER DATABASE tempdb MODIFY FILE with a larger size buys room at once with no impact on anyone. It is not undone by a restart: tempdb is recreated at its last configured size, and MS Docs on shrinking tempdb names an explicit MODIFY FILE size as what sets it. Give every data file the same size, or you have built the uneven set the per-file read warns about.

Kill the Session, If It Is User or Internal Objects

With a session number from the query above and a statement you can see, this is usually the right call, and the space comes back as the rollback completes rather than the instant you press enter. Two things to know first. A kill rolls the transaction back, and the rollback can take as long as the work took. And killing a session that is only spilling cancels a query somebody is waiting on, which is a judgement call, not a fix. How to Kill a SPID in SQL Server covers what the rollback costs, and KILL on MS Docs has the WITH STATUSONLY option for watching it.

Free the Version Store by Ending the Transaction That Pins It

The version store cannot be killed, shrunk or cleaned up on demand. Versions are kept until no transaction could still need them, so one long-running snapshot reader keeps every version generated since it started, whichever database made them, and a background task removes them periodically once it ends. The question is which transaction is pinning it:

SELECT  t.session_id,
        t.elapsed_time_seconds,
        t.is_snapshot,
        t.max_version_chain_traversed,
        ISNULL(s.program_name, '') AS program_name,
        ISNULL(s.status, '')       AS session_status
FROM    sys.dm_tran_active_snapshot_database_transactions AS t
LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = t.session_id
ORDER BY t.elapsed_time_seconds DESC;

At the moment the breakdown above read 94 MB of version store:

One row, and it is the whole 94 MB. Session 54 opened a snapshot transaction 135 seconds earlier and went idle without ending it. It read from one database, yet sys.dm_tran_version_store_space_usage showed 65 MB of versions from that database and 28 MB from a second database it never touched, both held. When it committed, the version store emptied within 24 seconds and free space rose by 95 MB.

Commit it, roll it back, or kill it. Get Open Transactions is the wider check for the open transactions that are not snapshot readers. A database with accelerated database recovery on keeps its versions in its own persistent version store rather than tempdb, and the transaction locking and row versioning guide on MS Docs lists what generates versions, which includes triggers and online index builds on databases where nobody turned snapshot isolation on.

DBCC FREEPROCCACHE Is Not a Fix

It will be suggested, and it frees no tempdb space at all: the plan cache is memory, and none of the 3 kinds of tempdb space live there. What it does is throw away every compiled plan on the instance, so on a busy box a space incident becomes a CPU incident. DBCC DROPCLEANBUFFERS is the same mistake. DBCC FREEPROCCACHE on MS Docs says what it is for, and it is not this.

Shrink Is the Last Resort, and Usually Does Nothing

A shrink can only release space that is already free, so while the thing that filled tempdb is still running it gives back nothing, and MS Docs on shrinking tempdb warns that ongoing activity can block it from completing at all. DBCC SHRINKFILE on MS Docs asks you to shrink only when necessary and never as regular maintenance. If you have already tried it and nothing happened, When Shrinking TempDB Just Will Not Shrink covers why and what works.

A restart does empty tempdb. It is also an outage for every database on the instance, it destroys the evidence of what filled it, and the cause is untouched, so it comes back. Keep it for when the instance is already unusable.

What Stops It Happening Again

Four changes, and the first two are configuration you can make at the next restart window.

• Equal files, sized for the drive, growth in MB. Every tempdb data file the same size and the same growth increment, growth in megabytes rather than a percentage, and the total pre-sized to most of the space you are willing to give it so growth is the exception. Get TempDB Configuration grades what you have against the core count on the server.
• Instant file initialization on. With the volume maintenance task right granted to the service account, a data file growth stops zeroing the new space, which turns a stall into an instant. TempDB is recreated at every startup, so it is worth more here than almost anywhere else. Instant File Initialization covers the grant and what it does for log files.
• A baseline, so you know what normal looks like. A tempdb incident takes an hour because nobody knows whether 40 GB is unusual for that server. Collect Capacity and TempDB Baselines writes the daily numbers somewhere you can look afterwards, and Get TempDB Hotspots names the repeat offenders rather than today’s.
• The workload, which is the only real fix. A spill is a bad estimate, a 60 GB version store is a transaction scope that is too wide, and a temp table holding 20 million rows is usually a query that should have filtered first. Table Variables vs Temp Tables is the one of those arguments with a measured answer. On SQL Server 2025, a tempdb space limit per workload group makes a runaway query fail with 1138 instead of filling tempdb for everyone.

Frequently Asked Questions