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.

Lance