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:
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 a...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
nd deletes many temporary objects as it runs:
Since the tempdb does not report its size properly after it has shrunk, the following query will get you the available space in tempdb:
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:
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:
[IMAGE]
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. Schroeder currently works for the gaming vendor IGT providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and nonprofit. Schroeder specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He 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.
