Why is tempdb full, and how can I prevent this from happening?

SQL Server allocates a database called tempdb, primarily for worktable / #temp table usage. Sometimes, you will have one of the following symptoms:

Source: MSSQLSERVER
Event ID: 17052
Description: The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Causes:

Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth.

Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:

• any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
• if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
• DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
• DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
• large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp  tables, table variables, and hashing can often require help from tempdb;
• any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;
  The following will tell you how tempdb's space is allocated:
  USE tempdb
  GO
  EXEC sp_spaceused

Short-term fix:

Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again.

To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE.

If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:

DBCC OPENTRRAN

Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : <number>'). Use that <number> in the following:

DBCC INPUTBUFFER(<spid>)

This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

KILL spid