Shrink tempdb database

Customer requirements:

This is a production environment where it is found that tempdb has surpassed 500GB in the dead of night.

Requirements Analysis:

We know that if you restart the SQL Server,tempdb, it will be automatically recreated, bringing the tempdb back to its original size. However, this is a production environment and it is not allowed to restart SQL Server.

Try:

Direct contraction of tempdb is always unsuccessful.

USE [tempdb]

GO

DBCC SHRINKFILE (Numbtempdev', 0, TRUNCATEONLY)-frees up all available space

GO

DBCC SHRINKFILE (Numbtempdev', 500)-shrinking to 500MB

GO

Solution:

In order to enhance the performance of tempdb, SQL Server 2005 and later versions will cache some IAM pages for future reuse. In this case, the IAM page must be released before its corresponding page can be released. Therefore, with DBCC FREESYSTEMCACHE, all unused cache entries are released from all caches, and then the tempdb is shrunk.

USE [tempdb]

GO

DBCC FREESYSTEMCACHE ('ALL')

GO

DBCC SHRINKFILE (Numbtempdev', 500)

GO

Finally shrunk to 500 MB. Success!

For DBCC FREESYSTEMCACHE, please refer to https://technet.microsoft.com/zh-cn/library/ms178529.aspx