Examining data files when SQL Server tempdb is full

If your SQL Server tempdb database is full, the data files could be the cause. Find out how to use the SQL Server process ID number, along with T-SQL queries, to find the source of any unexpected growth in data files.

As a database administrator (DBA), you will inevitably hear the phrase "The tempdb database is full." Sometimes

it is easy to identify the transaction that has caused the issue, but more often, the problem stems from a combination of several requests involving new code deployment or substantially increased data.

What does it mean when the tempdb is full?

When SQL Server's tempdb is full, upper management will often demand a fix, a group of developers will frantically deflect blame and junior DBAs will become afraid to touch anything at all.

As I tell administrators, the first rule of thumb is simple: Keep calm. Never let the scenario unfolding cause extra stress, as that can lead to critical mistakes.

Now that we have set up the scenario, let's address the question. The tempdb database consists of two parts: the data file(s) in the primary file group, and the tempdb log file(s). Either area could be the culprit, but the error message will tell you which part is full. Let's concentrate on the data file portion; we will address the log file in a later article.

How can I narrow down the source?

First, let's take a look at how to determine what takes up the most space, which server process ID number (SPID) we are dealing with and which host the request comes from. The query below will return the top 1000 SPIDs that are taking up space in the database. Keep in mind that the values returned are page counts. To make it easier, I included the calculations to come up with the memory values (in megabytes). Also, note that these counters are cumulative over the life of the SPID:

SELECT top 1000
s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_alloc_page_count > 0 or
su.internal_objects_alloc_page_count > 0)
order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count else su.internal_objects_alloc_page_count end desc

The next query is similar; it returns the top 1000 items in which SPIDs have de-allocated the most space. This query can be used to trace a process that loops, creates objects as it goes, or creates and deletes many temporary objects as it runs:

SELECT top 1000 s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_dealloc_page_count > 0 or
su.internal_objects_dealloc_page_count > 0)
order by case when su.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
su.user_objects_dealloc_page_count else su.internal_objects_dealloc_page_count end desc

Since the tempdb does not report its size properly after it has shrunk, the following query will get you the available space in tempdb:

SELECT sum(unallocated_extent_page_count) [Free_Pages],
(sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage

Once you have determined the SPID, you can determine what T-SQL is running with dbcc inputbuffer(SPID).

Let's presume you know the T-SQL code that ran, but you also need to know the temporary table(s) involved. You could run the following:

select * from tempdb.sys.objects where type = 'u'

Temporary tables originating from users in T-SQL should have the format of #YourDefinedTblName____UniqueID. This will help you identify the code involved. You could also join the SPID involved from the sys.dm_exec_requests command and use sys.dm_exec_sql_text(SQL_Handle) to get the line running at the time, but this requires a "polling loop" to monitor when the script is actually running.

Conclusion

With existing system tables and views, it can be difficult to narrow down the issue without advance preparation. The source of a full tempdb can be as simple as a single SPID or as complex as a combination of many sessions, but the processes that I've outlined above should help you narrow down which processes or combinations are causing problems.

ABOUT THE AUTHOR

Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.

This was first published in March 2009

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close