Hello everyone!
Today we're going to talk about "tempdb", a system database in Microsoft SQL Server that stores temporary data such as temporary tables and table variables , query execution plans, and intermediate query results. This database is essential to the proper functioning of SQL Server, and it is important to control its size. (it's weird with the words in French rather than in English but you understand, "temporary tables, variable table, query execution plan, query intermediate results, etc etc")
The problem we are going to 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.We right click on the "tempdb" and then => Tasks => Shrink => Files => Don't shrink anything, just look at the available space. If free space is high, this indicates that a poorly optimized query is being executed and "tempdb" does not free up available space.
If space is low, navigate to the "tempdb" and run a standard report, "Top Transactions by Age", to identify if many transactions are open, which could be causing the database to grow and not free up space.
If this returns nothing, you can run a script that displays all running processes and how much space they are using in 'tempdb'.
The following script should work:
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 of this information, you can begin to find the cause of this oversize problem and find out exactly "who" is causing it. or "what" uses space.
To temporarily correct this problem, simply restart SQL Server, which will clear the "tempdb" log file. Then you can modify the auto-growth settings and set a fixed size or deactivate this settings. Logically the problematic query will no longer be able to execute and we should find the culprit.
By following these steps, you can easily identify the cause of excessive growth of "tempdb" and resolve the problem.
I hope this article was helpful to you. See you soon!