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

At 2am this looks like the instance failing. Inserts fail, a report fails, the drive alert goes off, and the file that grew is tempdb. The thing worth knowing in the first minute is that tempdb has no owner. Every session on the instance shares it, so the session that received the error is almost never the session that caused the problem, and the database named in the error is almost never the database at fault.

That leaves two questions, in this order. Which session is holding the space, and which of the 3 kinds of space it is holding. Get the second one wrong and you will kill a session that was using none of it, or shrink a file that has nothing to give back.

The Message, If You Got One

Two messages come out of a tempdb with no room left. Both are severity 17 and both go 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, where %.*ls marks each value the server substitutes: the object, the database and the filegroup. In a tempdb incident the database reads tempdb and the filegroup reads PRIMARY, and the object name is usually one you will not recognise, because it is a worktable the engine named for itself rather than anything in your schema. Documented, not reproduced here: filling tempdb on purpose means putting a size cap on its files, and that is not a thing to do on an instance anyone else is using.

The sibling message is 1101, also severity 17, which opens “Could not allocate a new page for database” and names no object. Same cause, a different allocation path. If the database in your message is one of your own user databases instead of tempdb, you are on the wrong page, and Could Not Allocate Space Because the Filegroup Is Full (Error 1105) is written for that case, where the answers are autogrowth, the file cap and the drive.

You may also have no message at all. Plenty of these arrive as a disk alert, or as the storage team asking why one drive went from a third full to full inside an hour. Nothing below needs an error number.

The One Query: Which Session Is Using TempDB Right Now

Run this from any database on the instance. Two DMVs, one row per session that has allocated anything in tempdb, with the statement attached so you are not left guessing which one it is:

WITH tempdb_users AS
(
    SELECT  s.session_id,
            r.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 statement_start
    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
    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(r.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, statement_start
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, because a page is 8 KB. Here it is on a lab instance where one session is holding a temp table it filled and another has a static cursor open over 400,000 rows, with a third transaction generating row versions in the background:

session_id|status|wait_type|sess_user_mb|sess_internal_mb|task_user_mb|task_internal_mb|program_name|statement_start
----------|------|---------|------------|----------------|------------|----------------|------------|---------------
80|suspended|WAITFOR|0|0|0|73|SQLCMD|USE Lab1001_tempdbfull; SET NOCOUNT ON; DECLARE monthly_rollup
92|suspended|WAITFOR|0|0|35|0|SQLCMD|USE Lab1001_tempdbfull; SET NOCOUNT ON; CREATE TABLE #order_st

That is the answer. Not “tempdb is full”, but a session number, a statement and a size. Session 92 is sitting on 35 MB of temp table it built and has not finished with. Session 80 has 73 MB of worktable that nobody asked for by name, because a static cursor materialises its whole result set in tempdb. Three readings, and they send you three different ways:

• 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 that 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 the one above. A spill means the optimiser asked for less memory than the query 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 at all and so 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

Both rows in that capture report their space under task, and both report 0 under sess, which looks wrong until you know what each DMV counts. sys.dm_db_task_space_usage holds allocations belonging to tasks that are still live, and both of those batches are still open, so that is where their pages sit. sys.dm_db_session_space_usage holds what a session’s completed tasks allocated and have not given back, which is where the pages move to once the batch ends while the object is still in scope.

The practical consequence is the only part you need at 2am: query both, add them up, and never conclude a session is innocent from one pair alone. The task view also has a row per task rather than per session, so a parallel query appears several times, which is why the version above sums them. See sys.dm_db_session_space_usage and sys.dm_db_task_space_usage on MS Docs for the full column lists. 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:

total_mb|free_mb|user_objects_mb|internal_objects_mb|version_store_mb
--------|-------|---------------|-------------------|----------------
576|382|37|73|76

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 gives you one row per file, which answers the other question worth asking: are the files the same size and filling evenly. On the instance above they were: 8 files of 72 MB each, carrying 9 MB of version store and 9 MB of internal objects apiece, with free space between 45 MB and 49 MB across the set. That is what you want to see, because SQL Server allocates across tempdb files in proportion to their free space, so one undersized file among 8 fills first and raises 1105 while the rest sit half empty. That is the usual answer to “tempdb is full and the drive is not”. 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.

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. A manual ALTER DATABASE tempdb MODIFY FILE with a larger size buys you minutes with no impact on anyone, and it is reversible at the next restart, because tempdb is recreated every time the service starts. Match the size you give one file across all of them, or you have just created the uneven set that fills one file first.

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 before you do it. A kill rolls the transaction back, and the rollback can take as long as the work took, so the space may not drop for a while. 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 doing it and the part people skip, which is what the rollback then costs. KILL on MS Docs has the syntax and the WITH STATUSONLY option for watching the rollback.

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 reader under snapshot isolation keeps every version generated since it started, no matter which database made them. The cleanup task then removes them in the background, a minute at a time. The question to answer 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;

On the lab instance, at the moment the capture above read 76 MB of version store:

session_id|elapsed_time_seconds|is_snapshot|max_version_chain_traversed|program_name|session_status
----------|--------------------|-----------|---------------------------|------------|--------------
51|328|1|0|SQLCMD|running

One row, and that is the whole story of the 76 MB. Session 51 opened a transaction under snapshot isolation 328 seconds earlier and had not ended it, so every version generated anywhere on the instance since then had to be kept in case that session asked for it. No session shows the space in the first capture, because the version store is not charged to anyone. When that transaction finally ended, the version store went from 76 MB to 0 and free space went from 382 MB to 458 MB on the next read, which is the clearest demonstration there is that this part of tempdb answers to transactions and not to you.

Commit it, roll it back, or kill it, and the space comes back on the cleanup task’s own schedule rather than immediately. Get Open Transactions is the wider version of this check, for the open transactions that are not snapshot readers, and the transaction locking and row versioning guide on MS Docs explains what generates versions in the first place, which includes triggers and online index rebuilds on databases where nobody has turned snapshot isolation on at all.

DBCC FREEPROCCACHE Is Not a Fix

It will be suggested, usually by a search result, and it does nothing at all for tempdb space. The plan cache and tempdb are different memory and different files, so clearing plans frees no pages in any of the 3 categories above. What it does do is throw away every compiled plan on the instance, so the next execution of everything recompiles, which on a busy box turns a space incident into a CPU incident. The same applies to DBCC DROPCLEANBUFFERS. 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 running it while the thing that filled tempdb is still running gives back nothing and reports success. Even after the workload has gone, a shrink on tempdb often cannot move the pages it would need to move, because internal objects and version store pages are in use by somebody. MS Docs is blunt about the operation in general: use DBCC SHRINKFILE only when necessary, because shrink is a long-running and resource-intensive operation, and do not treat shrink operations as regular maintenance. If you have already tried it and nothing happened, When Shrinking TempDB Just Will Not Shrink covers why and what actually works, and DBCC SHRINKFILE on MS Docs carries the warning.

A restart does empty tempdb, because the files are recreated at startup at whatever size they are configured for. It is also an outage for every database on the instance, it loses the evidence of what filled it, and it will happen again next month because nothing about the cause changed. Keep it for the case where 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 set in megabytes rather than a percentage, and the total pre-sized to most of the space you are willing to give it so that growth is an exception rather than routine. A percentage on a file that has already grown large means every growth is bigger than the last, which is how an allocation that used to be instant becomes a stall. Get TempDB Configuration grades what you have against the core count on the server.
• How to Move TempDB Files in SQL Server, the incident that sends a reader to move the files in the first place
• 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 the growth from a stall into an instant. It applies to tempdb data files and to a tempdb that 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 not cover, which is log files.
• A baseline, so you know what normal looks like. The reason a tempdb incident takes an hour is that nobody knows whether 40 GB is unusual on this instance. Collect Capacity and TempDB Baselines writes the daily numbers somewhere you can look afterwards, and Get TempDB Hotspots is the one that 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. 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 that has a measured answer.

Worth knowing for the next incident that looks like this one but is not: tempdb contention and tempdb space are different problems with the same name. If sessions are slow but there is plenty of free space, you are looking at allocation page latches, not capacity, and PAGELATCH_EX and TempDB Contention is the page for that. The file count fix is the same, which is why the two get confused.

Common Questions