How do I shrink tempdb data files?
- How do I shrink TempDB files without restarting?
- What should I do if SQL Server TempDB is full?
- Why does TempDB grow so large?
- How do I change my TempDB size?
How do I shrink TempDB files without restarting?
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
What should I do if SQL Server TempDB is full?
When investigating a TempDB issue like this, most simply restart the SQL Server instance. It's easy to see why they do – the issue quite often locks up completely, and if a customer wants their server to work again ASAP, then a restart is almost inevitable. A restart will tackle the symptom, but not the cause.
Why does TempDB grow so large?
Tempdb growth is mainly due to poor performing queries, so you can use SQL Profiler and probably filter on Duration to determine if there are any stored procedures that are taking more than x seconds to execute.
How do I change my TempDB size?
The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that's your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance.
Related Questions
-
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago -
Anonymous2 weeks ago
Expert answer2 weeks ago