Thursday, March 1, 2012

How to Shrink Tempdb in SQLServer

Once in a while DBA’s will come across the situation that tempdb is full and no longer able to expand. You can avoid this situation by proactively monitoring the system with notification system setup for particular threshold.

To avoid this situation proactive monitoring is the best solution and it is always recommend to setup alerts for particular threshold.

It's highly recommended to disable "Autogrow" option for better performance. By default this option is enabled and it grows by 10% of its initial size. While performing auto growth, it will lock all applications and internal operations. This will also lead to fragmentation in the tempdb database.

Each time the SQL Server service starts, the tempdb is newly created by copying from the model database and inheriting some database configurations from it. The default size of tempdb is 8MB

You can try below solutions to shrink the databases.

Method 1: Shrink database using DBCC SHRINKDATABASE.

For ex:-

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

Method 2: Shrink the tempdb data file or log file

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

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

Errors while shrinking tempdb
If you have any active transactions in tempdb then you may receive below consistency 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.

Satishbabu Gunukula

1 comment:

  1. Most of the SQL Server users suffer with the same issue. This issue exists in all versions of SQL Server & it is EXTREMELY annoying. Shrinking the database is a good & useful tip. But also make sure that your databases should not set to auto-shrink. Databases that are automatically set to auto-shrink, can encounter real performance problems.