Page 1 of 1

How do i shrink TempDB?

Posted: Sat Jun 29, 2019 9:58 am
by DaveP5
Hi, how do i shrink TempDB?

Re: How do i shrink TempDB?

Posted: Sat Jun 29, 2019 10:00 am
by sgfive
Hi, there are a few methods that allow you to shrink TempDB.

Method 1

Shrink database using DBCC SHRINKDATABASE.

DBCC SHRINKDATABASE (tempdb,’’)
For ex:-
DBCC SHRINKDATABASE(tempdb,40)

This command shrinks the tempdb database as a whole with specified percentage.

Method 2: Shrink the tempdb data file or log file

Use tempdb
GO
--shrink the data file
DBCC shrinkfile (tempdb_data,’< target_size_in_MB’>)
GO
--shrink the log file
DBCC shrinkfile (tempdb_log, ’)

Ex:-
DBCC shrinkfile (tempdb_data, 5000);

Method 3: shrink the tempdb data file or log file using T-SQL Command

-- Shrink the data file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_data', SIZE = target_size_in_MB)

-- Shrink the log file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_log', SIZE = target_size_in_MB)

Where target_size_in_MB= Desired target size for the data/log file

Below are some possible errors that can occur when attempting to shrink tempdb:

If you have any active transactions in tempdb then you may receive below errors:

1. Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

This error may not be an indicative of any corruption, but it causes the shrink operation to fail.

2. Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

This 8909 error indicates tempdb corruption. You can clean up the consistency errors by restarting the SQL Server. If you still see any errors then restore the database from valid backup.

3. DBCC SHRINKFILE: Page 1:456120 could not be moved because it is a work file page.

This error indicates that Page could not be moved because it is a work file page. You can release the cached objects by running “DBCC FREEPROCCACHE”. Now try shrink tempdb once again to release the free space.