What should we do if the Tempdb space in SQLServer grows abnormally and occupies a lot of space?

What to do about the abnormal growth and occupation of Tempdb space in SQLServer? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

I. problem phenomenon

We have a mass production SQLServer that issues an out-of-disk space alarm in the middle of the night. When I log on to check, I find that tempdb has exceeded 500GB.

Second, the reason analysis

After communicating with AP user, AP user told us that there was an abnormal growth of tempdb due to a problem with AP logic.

III. Solutions

1. If the SQL Server,tempdb can be restarted, it will be recreated automatically, thus returning the tempdb to its original size. But the mass production environment should not be restarted unless it is a last resort.

2. Shrink tempdb directly (but never succeed).

(1) order

USE [tempdb]

GO

DBCC SHRINKFILE (Numbtempdb.mdf', 0, TRUNCATEONLY)-frees up all available space

GO

DBCC SHRINKFILE (Numbtempdb. MDF', 500)-shrinks datafile to 500MB

GO

DBCC SHRINKFILE (Numbtemplog.ldf', 10)-shrinks the log to 10MB

GO

(2) graphical interface

3. If the tempdb cannot be shrunk, you can use the following methods to deal with it before shrinking.

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 (Numbtempdb.mdf', 500)

GO

After reading the above, have you mastered the method of abnormal growth and occupation of Tempdb space in SQLServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!