What To Do When tempdb Is Full

By Lance Harra on 23 August 2001 | 3 Comments | Tags: Administration


Sim writes "I would like to know if tempdb has become full and what to do if you cannot truncate the log? I want to understand the process why you cannot truncate it."

Let's start with a breif description of tempdb and it's default settings. The tempdb database is a system database that contains temporary tables created by the server and by the user. The database is recreated each time SQL server is restarted. If you are running SQL 7 or later the default settings for the tempdb are unlimited filesize and autogrow by 10%. SQL Server locks tempdb while it is expanding so correct sizing of your tempdb is important. If you run out of space on a SQL7 or later and you have not changed these settings then you need to look at hardrive space.

Truncating the log just removes inactive entries form the log. TempDB is set to remove the inactive entries automatically (SQL7 and earlier the 'trun. log on chkpt' option is set and the SQL2000 recovery mode is set to simple). These settings force inactive log entries to be removed from the log during a checkpoint operation. Books online has a good article explaining truncating the transaction log.

On versions prior to 7.0 or if you set a maximum size for tempdb you will get an error 1105 (filegroup full) or a 9002 (log file full) when you fill up tempdb. Use performance monitor to watch the amount tempdb space in use. Set an alert to notify you when the usage crosses a threshold for a period of time.

Redesign queries to work on smaller sets of data at a time. Break one large transaction into several smaller transactions if possible. In SQL2000 try table varibles instead of temporary tables. These varaibles are handled in memory not in tempdb. Expand the tempdb by adding files or by moving it to another hardrive.

The procedure for moving tempDB depends upon the version of SQL you are running. The following links discuss how to move the tempdb:

How to Move Tempdb to a Different Device (SQL Server 6.5)

How to Performance Tune the Microsoft SQL Server tempdb Database (SQL Server 7/2000)

I hope this answers your question about TempDB.

Lance

Discuss this article: 3 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Using xp_ReadErrorLog in SQL Server 2005 (12 May 2008)

Moving the tempdb database (5 November 2007)

Other Recent Forum Posts

CHECKDB Failing (9 Replies)

Turning Rows into Columns (10 Replies)

sql query help eith grouping sets (2 Replies)

SQL Server 2008 in Ilford UK (3 Replies)

Check if a primary keys exists (4 Replies)

SSRS Matrix Report (2 Replies)

Get Mode Average? (2 Replies)

Remote Connection Problem (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -