//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2000.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2000 :: Why is tempdb full, and how can I prevent this from happening?


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

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 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.

 
Other points of analysis 
 
The following will tell you how tempdb's space is allocated: 
 
USE tempdb 
GO 
EXEC sp_spaceused
 
The following should give you some clues as to which table(s) consume most of the space in the data file(s) -- this will help you narrow down any transactions that are either taking a long time or repeatedly being left in limbo: 
 
USE tempdb 
GO 
 
SELECT name 
    FROM tempdb..sysobjects 
 
SELECT OBJECT_NAME(id), rowcnt 
    FROM tempdb..sysindexes 
    WHERE OBJECT_NAME(id) LIKE '#%' 
    ORDER BY rowcnt DESC
 
The higher rowcount values will likely indicate the biggest temporary tables that are consuming space. And while it won't tell you everything, since tempdb is used for internal I/O and other processes such as sorting, it may help you narrow down the stored procedure(s) that are causing the growth (you can query INFORMATION_SCHEMA.ROUTINES for ROUTINE_DEFINITION LIKE '%#table_name%' from above).  
 
In addition to this, you can use Profiler to watch for events like database file auto grow and log file auto grow. If this is happening often, then you know that the space you've allocated to tempdb is not sufficient. 
 
You can also watch performance monitor's counter for PhysicalDisk: CurrentDiskQueueLength on the drive where tempdb exists. If this number is consistently greater than 2, then there is likely a bottleneck in disk I/O. 
 

 
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. See KB #256650, KB #272318 and KB #307487 for more information. 
 
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 OPENTRAN -- or DBCC OPENTRAN('tempdb')
 
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(<number>)
 
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 <number>
 

 
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, see the suggestion from Dinesh at http://www.tkdinesh.com/faq/ans/tempdbsh...
     
  • If possible, put tempdb on its own physical disk, array or disk subsystem (see KB #224071 for more information). 
     
  • To prevent tempdb log file growth, make sure tempdb is in simple recovery mode (this allows the log to be truncated automatically). To check if this is the case: 
     
    -- SQL Server 7.0, should show 'trunc. log on chkpt.' 
    -- or 'recovery=SIMPLE' as part of status column: 
     
    EXEC sp_helpdb 'tempdb' 
     
    -- SQL Server 2000, should yield 'SIMPLE': 
     
    SELECT DATABASEPROPERTYEX('tempdb', 'recovery')
     
    If the database is not set to simple recovery, you can force it so as follows: 
     
    ALTER DATABASE tempdb SET RECOVERY SIMPLE
     
  • 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. 
     
  • Check out the WebCast in KB #834846 for some ideas from Microsoft about administering and maintaining TempDB.

Related Articles

Are there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I load text or csv file data into SQL Server?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?

 

 


Created: 2/5/2003 | Last Updated: 9/12/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (174)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...