How to increase the size of TEMPDB

TEMPDB is a build-in temporary database. It is used for:

  • Bulk operations
  • Common table expression queries
  • Cursors
  • Event notification
  • Indexes/sort_in_tempdb
  • Very large object (varchar,text, image...)
  • Row versioning
  • Triggers
  • XML

Microsoft recommends that you move the **TEMPDB** database, to it's own drive.

  • TEMPDB needs to be large enough to hold all the temporary information.
  • If TEMPDB runs out of disk space, you will get one of the following error messages:
    1. Error: 605, Severity: 21, State: 3. Attempt to fetch logical page XXXXXX in database 2 failed. It belongs to allocation unit YYYYYYYYYY not to ZZZZZZZZZZZZZZZZZ.
    2. Error: 5243, Severity: 22, State: 1. An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8.
    3. Error: 3449, Severity: 21, State: 1. SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

Get the current size of TEMPDB

USE master;
go

SELECT name AS 'File',
       CAST(CAST(SIZE*1.0/128 AS DECIMAL(9,2)) AS VARCHAR(12)) + ' Mb' AS 'File Size',
       CASE max_size WHEN 0 THEN 'Off'
                     WHEN -1 THEN 'On'
                     ELSE 'Will grow to 2 Tb'
       END AS 'Auto Growth',
       growth AS 'Growth',
       CASE WHEN growth = 0 THEN 'size is fixed and will not grow'
            WHEN growth > 0 and is_percent_growth = 0 THEN 'Growth in 8Kb pages'
            ELSE '%'
       END AS 'Increment'
FROM tempdb.sys.database_files;
GO
File        File Size       Auto Growth   Growth      Increment
----------- --------------- ------------- ----------- -----------
tempdev     10.00 Mb        On            10          %
templog     0.50 Mb         On            10          %

(2 row(s) affected)

Increase the size of TEMPDB

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', SIZE = 10MB);
GO

Command(s) completed successfully.

Verification

The verification can be done either with:

  1. the previous code show the file size.
  2. sp_spaceused
USE tempdb
GO
EXEC SP_SPACEUSED;
go
database_name    database_size      unallocated space
---------------- ------------------ ------------------
tempdb           10.50 MB           8.84 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
1184 KB            496 KB             592 KB             96 KB