Why is TempDB growing so large?

Question

Uncontrolled TempDB growth. There are many reasons for uncontrolled TempDB growth events. Much like your operating system has a page file to handle memory overflows, SQL Server uses TempDB like a page file. The most common occurrence of this is when a query “spills” to TempDB. 22 мар. 2021 г.

How do I reduce my TempDB size?

use tempdb go dbcc shrinkfile (tempdev, 'target size in MB') go -- this command shrinks the primary data file dbcc shrinkfile (templog, 'target size in MB') go -- this command shrinks the log file, examine the last paragraph.

What is taking up TempDB space?

TempDb is being used by a number of operations inside SQL Server, let me list some of them here: Temporary user objects like temp tables, table variables. Cursors. Internal worktables for spool and sorting.

How do I reduce TempDB without resetting?

Shrink Tempdb without restarting SQL Server1Method 1 :2DBCC FREEPROCCACHE.3DBCC DROPCLEANBUFFERS.4DBCC FREESYSTEMCACHE ('ALL')5DBCC FREESESSIONCACHE.6DBCC SHRINKDATABASE(tempdb, 10)7Method 2 :Shrink Tempdb without restarting SQL Server - Sri's SQLDBA Blog

How big should my TempDB be?

TempDB should be sized based on the size of the drive it's on (and it should be on its own drive). Generally speaking you should have one TempDB file per CPU core (up to 8) and one TempDB_Log file. So... divide total space on the drive by (number of CPU cores + 1).

Expert · Answer