Examining data files when SQL Server tempdb is full

Source Link

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, whichserver 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 elsesu.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 whensu.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
su.user_objects_dealloc_page_count elsesu.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_requestscommand 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.

Advertisements

One response to this post.

  1. […] Examining data files when SQL Server tempdb is full […]

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: