Solution of fixing tempdb is full-hnynes-ChinaUnix博客

编程开发分享hnynes.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

hnynes

博客访问： 310851
博文数量： 163
博客积分： 0
博客等级：民兵
技术积分： -40
用户组：普通用户
注册时间： 2017-03-08 00:28

文章分类

全部博文（163）

TEST（1）
数据处理（107）

BI（1）

postgresql（0）

hadoop（2）

架构设计与优化（1）

oracle（2）

SAS（0）

SQLite（1）

MySql（44）

SQL Server（55）
新情文章（7）
English Learn（10）
操作系统（19）

LinuxShell（1）

清除计算机病毒（0）

Linux（11）

Windows（7）
程序开发语言（18）

Android（2）

Ruby on Rails（1）

python（1）

windows ping（0）

.NTE（0）

Java（1）

C++（8）

C（1）
未分配的博文（1）

文章存档

2015年（2）

2014年（35）

2013年（28）

2012年（30）

2011年（22）

2010年（14）

2009年（8）

2008年（13）

2007年（11）

我的朋友

相关博文

Solution of fixing tempdb is full

分类： SQLServer

2013-09-29 10:38:10

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 , and 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) : '). Use that in the following:

					DBCC INPUTBUFFER()
				

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 
				

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 .
If possible, put tempdb on its own physical disk, array or disk subsystem (see 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 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 for some other general ideas on efficiency.
Check out the WebCast in for some ideas from Microsoft about administering and maintaining TempDB.