What To Do When tempdb Is Full

By Lance Harra on 23 August 2001 | 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.


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

CASE Statement EXISTS Query Check Assist (4h)

Managed Instance - Storage Blob Container Backup file Upload issue (17h)

Need help with nested list in SQL (2d)

Can't restore bak file from MS SQL Server 2000 (4d)

Connecting to SQL Server from externally with proxy server (4d)

How to Find if Java is Installed (4d)

How To Insert Title At Start of SQL Results and Between Another SQL Result Sets (4d)

Help with sql query find combinations that are not equal, please (5d)

- Advertisement -