Dec
25
2012

Troubleshooting Insufficient Disk Space in tempdb

Text Reference: http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

Some tips for minimizing tempdb utilization

1. Use fewer #temp tables and @table variables

2. Minimize concurrent index maintenance, and avoid the SORT_IN_TEMPDB option if it isn’t needed

3. Avoid unnecessary cursors; avoid static cursors if you think this may be a bottleneck, since static cursors use work tables in tempdb – though this is the type of cursor I always recommend if tempdb isn’t a bottleneck

4. Try to avoid spools (e.g. large CTEs that are referenced multiple times in the query)

5. Don’t use MARS

6. Thoroughly test the use of snapshot / RCSI isolation levels – don’t just turn it on for all databases since you’ve been told it’s better than NOLOCK (it is, but it isn’t free)

7. In some cases, it may sound unintuitive, but use more temp tables. E.g. breaking up a humongous query into parts may be slightly less efficient, but if it can avoid a huge memory spill to tempdb because the single, larger query requires a memory grant too large…

8. Avoid enabling triggers for bulk operations

9. Avoid overuse of LOB types (max types, XML, etc.) as local variables

10. Keep transactions short and sweet

11. Don’t set tempdb to be everyone’s default database –

 

You may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over – for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you’re using them you can’t dictate how and when they use tempdb.

 

Below query (taken from http://www.sqlservercentral.com/scripts/tempdb/72007/) will list down active sessions using tempdb along with query text, you can schedule a job to collect the data at frequent intervals and store in a table or csv file to narrow down the issue,

 

;WITH task_space_usage AS (


— SUM alloc/delloc pages


SELECT session_id,

request_id,


SUM(internal_objects_alloc_page_count)
AS alloc_pages,


SUM(internal_objects_dealloc_page_count)
AS dealloc_pages


FROM
sys.dm_db_task_space_usage
WITH (NOLOCK)


WHERE session_id <>
@@SPID


GROUP
BY session_id, request_id

)

SELECT TSU.session_id,

TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

EST.text,


— Extract statement from sql text


ISNULL(


NULLIF(


SUBSTRING(

EST.text,

ERQ.statement_start_offset / 2,


CASE
WHEN ERQ.statement_end_offset < ERQ.statement_start_offset


THEN 0


ELSE( ERQ.statement_end_offset ERQ.statement_start_offset )
/ 2 END


),


), EST.text


)
AS [statement text],

EQP.query_plan

FROM task_space_usage AS TSU

INNER
JOIN
sys.dm_exec_requests ERQ WITH (NOLOCK)


ON TSU.session_id = ERQ.session_id


AND TSU.request_id = ERQ.request_id

OUTER
APPLY
sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER
APPLY
sys.dm_exec_query_plan(ERQ.plan_handle)
AS EQP

WHERE EST.text IS
NOT
NULL
OR EQP.query_plan IS
NOT
NULL

ORDER
BY 3 DESC;

 

Other queries which can help you troubleshoot tempdb full are,

–Determining the Amount of Free Space in tempdb

SELECT
SUM(unallocated_extent_page_count)
AS [free pages],

(SUM(unallocated_extent_page_count)*1.0/128)
AS [free space in MB]

FROM
sys.dm_db_file_space_usage;

 

–Determining the Amount Space Used by the Version Store

SELECT
SUM(version_store_reserved_page_count)
AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128)
AS [version store s
pace in MB]

FROM
sys.dm_db_file_space_usage;

 

–Determining the Longest Running Transaction

SELECT transaction_id

FROM
sys.dm_tran_active_snapshot_database_transactions

ORDER
BY elapsed_time_seconds DESC;

 

–Determining the Amount of Space Used by Internal Objects

SELECT
SUM(internal_object_reserved_page_count)
AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128)
AS [internal object space in MB]

FROM
sys.dm_db_file_space_usage;

 

–Determining the Amount of Space Used by User Objects

SELECT
SUM(user_object_reserved_page_count)
AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128)
AS [user object space in MB]

FROM
sys.dm_db_file_space_usage;

 

–Determining the Total Amount of Space (Free and Used)

SELECT
SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

 

 

Useful Articles (Must read):

http://msdn.microsoft.com/en-us/library/ms176029(v=sql.100).aspx

http://download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc

http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: