MSSQL - tempdb Takes Up Too Much Space - Disk FULL

Hello everyone!

Today, we are going to talk about “tempdb,” a system database in Microsoft SQL Server that stores temporary data such as temporary tables, table variables, query execution plans, and intermediary query results. This database is essential for the proper functioning of SQL Server, and it is important to control its size.

The issue we will address concerns the excessive growth of the “tempdb” database.

To determine the cause of this problem, the first step is to launch SQL Server Management Studio and access the “tempdb” database.

Right-click on “tempdb” and then go to Tasks => Shrink => Files => Do not reduce anything yet, just check the available space. If the available space is high, it indicates that an unoptimized query is running, and “tempdb” is not releasing available space.

If the available space is low, access the “tempdb” database and run a standard report, “Top Transactions by Age”, to identify if many transactions are open, which could cause the database to grow and prevent space from being released.

If this does not return any results, you can run a script that displays all running processes and the amount of space they are using in “tempdb.”

The following script should work:

Who’s Using All that Space in tempdb, and What’s their Plan? - littlekendra.com

select
    t1.session_id
    , t1.request_id
    , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
    , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
    , host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
    , s1.login_name
    , s1.status
    , s1.last_request_start_time
    , s1.last_request_end_time
    , s1.row_count
    , s1.transaction_isolation_level
    , query_text=
        coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
          (CASE WHEN statement_end_offset = -1THEN LEN(CONVERT(nvarchar(max),text)) * 2ELSE statement_end_offset
              END - t2.statement_start_offset)/2)
        FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
    , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
from
    (Select session_id, request_id
    , task_alloc_pages=sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count)
    , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
    from sys.dm_db_task_space_usage
    group by session_id, request_id) as t1
left join sys.dm_exec_requests as t2 on
    t1.session_id = t2.session_id
    and t1.request_id = t2.request_id
left join sys.dm_exec_sessions as s1 on
    t1.session_id=s1.session_id
where
    t1.session_id > 50 -- ignore system unless you suspect there's a problem there
    and t1.session_id <> @@SPID -- ignore this request itself
order by t1.task_alloc_pages DESC;
GO

Once you have all this information, you can start identifying the root cause of the excessive size issue and determine exactly “who” or “what” is using the space.

To temporarily fix this issue, simply restart SQL Server, which will clear the “tempdb” log file. Then, you can modify the auto-growth settings to set a fixed size or disable this setting. Logically, the problematic query will no longer be able to execute, and we should be able to identify the culprit.

By following these steps, you can easily identify the cause of “tempdb”’s excessive growth and resolve the problem.

I hope this article was helpful. See you soon on the blog!