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
An error message in Query Analyzer:
Server: Msg 8624, Level 16, State 1
Internal SQL Server error
or
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.
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;
- use of an ODBC DSN with the option ‘create temporary stored procedures’ set can leave objects there for the life of the connection.
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.
Long-term prevention
Here are some suggestions for maintaining a healthy tempdb:
- Make sure that tempdb is set to autogrow — do *NOT* set a maximum size for tempdb. If the current drive is too full to allow autogrow events, then buy a bigger drive, or add files to tempdb on another device (using ALTER DATABASE) and allow those files to autogrow. You will need at least one data file and at least one log file in order to avoid this problem from halting your system in the future.
- For optimal performance, make sure that its initial size is adequate to handle a typical workload (autogrow events can cause performance to suffer as it allocates new extents). For an approach to setting a non-default size for tempdb
- If possible, put tempdb on its own physical disk, array or disk subsystem
- To prevent tempdb log file growth, make sure tempdb is in simple recovery mode (this allows the log to be truncated automatically).
- Use SQLOLEDB, not ODBC / DSN for database access (for VB / ASP, see Article #2126 for sample connection strings).
- Try to make sure you have covering indexes for all large table that are used in queries that can’t use a clustered index / index seek.
- Batch larger heavily-logged operations (especially deletes) that *might* overflow into tempdb into reasonable ‘chunks’ of rows, especially when joins are involved.
- Pore over your code for potential uncommitted transactions and other elements from the list at the top of the page.
- In general, try to make your code as efficient as possible… avoid cursors, nested loops, and #temp tables if possible. See Article #2424 for some other general ideas on efficiency.