Tuesday, July 25, 2006
Why is tempdb full, and how can I prevent this from happening?
#2446 : Why is tempdb full, and how can I prevent this from happening?: " Why is tempdb full, and how can I prevent this from happening?
http://www.aspfaq.com/2446 created: 2003-02-05 last updated: 2005-09-12 22:03 this article is printer friendly
Note that the majority of this article describes symptoms and workarounds for any database that is larger than you think it should be; it is not applicable only to tempdb.
SQL Server allocates a database called tempdb, primarily for worktable / #temp table usage. Sometimes, you will have one of the following symptoms:
* An error message in the event log:
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.
* Or you will notice that the files are much bigger than they should be -- by using EXEC sp_spaceused, looking at the taskpad view in Enterprise Manager, seeing the MDF/LDF files themselves within Windows Explorer, or being alerted by monitoring software like SiteScope or Quest Spotlight.
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 "
http://www.aspfaq.com/2446 created: 2003-02-05 last updated: 2005-09-12 22:03 this article is printer friendly
Note that the majority of this article describes symptoms and workarounds for any database that is larger than you think it should be; it is not applicable only to tempdb.
SQL Server allocates a database called tempdb, primarily for worktable / #temp table usage. Sometimes, you will have one of the following symptoms:
* An error message in the event log:
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.
* Or you will notice that the files are much bigger than they should be -- by using EXEC sp_spaceused, looking at the taskpad view in Enterprise Manager, seeing the MDF/LDF files themselves within Windows Explorer, or being alerted by monitoring software like SiteScope or Quest Spotlight.
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 "
