Shrinking tempDB

Question

Shrinking tempDB

bdragoo

Mr or Mrs. 500

Points: 554
More actions
December 18, 2008 at 6:47 am

#125106

We had some deadlocks and a couple of runaway processes that caused the tempDB to increase to 199GB, and this is putting a strain on our system. When I try to shrink the database, it says it can release 99% of space, but when I run the process, nothing appears to happen.
Why am I unable to reduce the space of tempDB? It is in simple recovery mode, and we have stopped the runaway processes...should I restart SQL.exe?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Restarting SQL Server should clear tempdb, you could try backup log tempdb with no_log which is a little less dramatic and could work.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Tempdb is in simple recovery so backup log won't help in that case, and if it is the data file and not the log file that is to large backup log won't help either. Restarting the SQL Server Service is the fastest way to clear your problem. I'd recommend that you monitor the size of your files and their free space so that you can identify that this is a potential issue before you get to a tip over point and it is to late.