Minimize tempdb size

from the Artful SQL Server & Access Tips List

SQL Server automatically creates a database named tempdb for use as a workspace. When you specifically create a temporary table within a stored procedure, for example (CREATE TABLE #MyTemp), the SQL engine creates it in tempdb regardless of which database you are actually using. In addition, whenever you sort a large result set using ORDER BY or GROUP BY, or use UNION, or perform a nested SELECT, if the data is larger than the memory available, the SQL engine creates working tables within tempdb. The SQL engine also uses tempdb whenever you run DBCC REINDEX or add a clustered index to an existing table. In fact, virtually any ALTER TABLE command against a large table will eat up a significant amount of space in tempdb.

Ideally, SQL cleans up after itself, destroying the temporary tables after completion of the specific operation. However, several things can go wrong. If your code creates a transaction and fails to commit it or roll it back, orphaned objects will be left behind in tempdb. And, it consumes a lot of space to run DBCC CHECK against a large database. You may also find that tempdb is much larger than it should be. You may even receive an error message that tells you SQL has run out of disk space.

There are things you can do immediately to remedy this situation, but you must realize that other steps are required to protect yourself in the longer term.

The simplest thing you can do to shrink tempdb to its minimum size is to shut down the SQL engine and then restart it. In a mission-critical situation, this may not be easy; on the other hand, if you're experiencing this problem, you're already in an impossible situation, so my advice is to bite the bullet—deliver the bad news to your superiors and get to work.

With luck, you'll have another disk available, where you can place tempdb. 

USE master
GO
ALTER DATABASE tempdb modify file (
  name = tempdev, filename = 'NewDrive:\Path\tempdb.mdf'
)
GO
ALTER DATABASE tempdb modify file (
  name = templog, filename = 'NewDrive:\Path\templog.ldf')
GO
There are three other tempdb properties that you should also check: the auto-growth flag, the initial size, and recovery mode. Here are tips to follow for each property:

Auto-growth flag: Make sure this flag is set to True.

Initial size: Set the initial size of tempdb according to what you consider a typical workload. If a lot of users are using the system to GROUP BY, ORDER BY, or perform aggregates on large tables, then your typical workload will be large. While you have the server offline, you might want to check whether the log files are on the same disk as the data files. If so, you should move them to the new disk, using the same commands but naming the relevant database.

Recovery mode: Setting the recovery mode to True tells SQL to automatically truncate tempdb's log file (i.e., after each use of the table). To find out which recovery mode has been set on tempdb, use: 

SELECT DATABASEPROPERTYEX('tempdb','recovery')
There are three possibilities: simple, full, and bulk-logged. To change the setting, use: 
ALTER DATABASE tempdb SET RECOVERY SIMPLE
These steps will optimize your system's use of tempdb. Besides solving the disk space problem, you will almost certainly notice an increase in performance.

Last updated 23 Apr 2024
Return to the Artful SQL Server & Access Tips page