Temdb Growing

Question

Hi

Does the tempdb gets automatically truncated every evening?

How do you check what is filling up the tempdb?

How can I prevent the Tempdb not to get full?

Answer 1

-- Current state of Transaction log usage for TempDB
select log_reuse_wait_desc from sys.databases where database_id = 2

What is the output of the above command?

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 2

Hi Sudeepta

Any relations to the legend Ganguly ;-)

The output is "Active_transaction"

I am also running a trace for SP:completed and RPC:completed for the duration of 2000(2 seconds).  Should I then look at CPU time to see whats taking up the space?

Regards,

Michael

Answer 3

Hi 

If I restart my server, will it free up space in the tempdb?

Regards,

Michael

Answer 4

It will but it also will purge all the interesting information gathered by DMVs since last restart. Next time, for active sessions with a valid sql handle:

with cte as (
SELECT session_id, request_id,
             SUM(internal_objects_alloc_page_count)/128 AS 'internal_allocations_Mb',
             SUM(internal_objects_dealloc_page_count)/128 AS 'internal_deallocations_Mb',
             SUM(user_objects_alloc_page_count)/128 AS 'user_allocations_Mb',
             SUM(user_objects_dealloc_page_count)/128 AS 'user_deallocations_Mb'
		FROM   sys.dm_db_task_space_usage		
		GROUP BY session_id, request_id
 )
SELECT REQ.session_id, REQ.request_id, 
cte.internal_allocations_Mb, cte.internal_deallocations_Mb, 
cte.user_allocations_Mb,cte.user_deallocations_Mb, 
SQL.text
FROM cte JOIN sys.dm_exec_requests REQ ON cte.session_id = REQ.session_id
AND cte.request_id = REQ.request_id
CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) SQL
WHERE REQ.session_id <> @@spid
ORDER BY cte.user_deallocations_Mb DESC, cte.internal_allocations_Mb  DESC

---

David B.

Answer 5

The output is "Active_transaction"

Is it perhaps the tempdb log file that is growoing?  A long-running open transaction will prevent committed transactions from being removed from the log in a timely manner and cause the log to grow.  Try running DBCC OPENTRAN to identify the culprit:

USE tempdb;
DBCC OPENTRAN();

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Hello Michael,

Once the transaction is completed, there will be space for other transactions. Why you want to restart the server?

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 7

Hi David 

Thank you for the query.  

To understand what I am looking for the output.  one of my sessionID "336" is using "4896" in Internal_allocation_MB.

So is that telling me that its writing  4.78GB to the tempdb.

I also looked at sp_who2 active to see who is running the query so thats kwl.

Answer 8

Thanks Dan

Will this tell me that this transaction is not commited

Answer 9

Internal allocation usually refers to aggregation (ORDER BY, HASH JOIN spills, GROUP BY, UNION, DISTINCT, etc...), checkdb facts, index sorting if specified... You'd have to check the query plan for this query :

with cte as (
SELECT session_id, request_id,
             SUM(internal_objects_alloc_page_count)/128 AS 'internal_allocations_Mb',
             SUM(internal_objects_dealloc_page_count)/128 AS 'internal_deallocations_Mb',
             SUM(user_objects_alloc_page_count)/128 AS 'user_allocations_Mb',
             SUM(user_objects_dealloc_page_count)/128 AS 'user_deallocations_Mb'
		FROM   sys.dm_db_task_space_usage		
		GROUP BY session_id, request_id
 )
SELECT REQ.session_id, REQ.request_id, 
cte.internal_allocations_Mb, cte.internal_deallocations_Mb, 
cte.user_allocations_Mb,cte.user_deallocations_Mb, 
SQL.text, P.query_plan
FROM cte JOIN sys.dm_exec_requests REQ ON cte.session_id = REQ.session_id
AND cte.request_id = REQ.request_id
CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) SQL
CROSS APPLY sys.dm_exec_query_plan(plan_handle) P
WHERE REQ.session_id <> @@spid
ORDER BY cte.user_deallocations_Mb DESC, cte.internal_allocations_Mb  DESC;

I may add that all the sys.dm_db_%%_space_usage DMVs apply to all databases since SQL Server 2012, so if you are on SQL Server 2012, you'd want to prefix these views by tempdb.

---

David B.

Answer 10

DBCC OPENTRAN will return the oldest active uncommitted transaction, if one exists.  It could be that there is a misbehaving application that starts a transaction but doesn't commit for an extended period.  You'll need to identify and remediate the problem app to address the root cause; transactions should be as short as possible.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 11

but I looked at the SPID and the transaction has been suspended.  How can I commit or rollback the transaction to free up space?

Answer 12

stupid question.  Can I kill the SPID to free up space?

Answer 13

Yes, killing the SPID will force a rollback and allow the logspace up to that point to be reused.  Be aware that you may have later uncommitted transactions too.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 14

Ok kwl thanks for that.  No then I dnt want to risk killing a uncommitted transaction