sql server tempdb size issue

This article save me today, really helpful.

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

 

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.
posted @ 2012-12-19 13:53  HEUYANG  阅读(257)  评论(0)  编辑  收藏  举报