asked
tempdb become too large too soon
the tempdb on one of my server is approaching 239 GB. It has been just two weeks before which it was at 117GB. The total drive size allocated is 250 GB. I need to find a way to shrink tempdb.
You need to find what is growing TempDB instead of shrinking it. Please look at my answer here --
https://ask.sqlservercentral.com/questions/119224/the-tempdb-size-keeps-growing.html
thanks everyone. I was able to reclaim 129GB of tempdb space. Still trying to figure what causes the rapid growth.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
There'll be a reason why your tempdb is that big - perhaps, rather than shrinking it immediately, it would be better to figure out what that reason is. It may well be that the reason it's that big is that's the size it needs to be. So. Why is tempdb the size it is? Do you have a long-running transaction building a big temporary table there? Do you have some big indexes that get rebuilt in TempDB? Do you have other processes / applications creating normal tables in TempDB and not tidying up after themselves?
You can shrink tempdb with worries of it corrupting. Paul Randal wrote a [blog](
http://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/) updating everyone of the changes since SQL 2000. However your larger issue is finding out what is causing tempdb to grow out of control like it is. Is this new behavior? What recent changes have been made that would cause this amount of spill or temp table usage to require a 239GB tempdb file? Steve Jones wrote a good [article](
http://www.sqlservercentral.com/blogs/steve_jones/2009/11/30/what-s-using-space-in-tempdb/) providing some scripts to use to see what is consuming your tempdb. This should help find where the problem is coming from.
@Vivek: Shrink wont help you as that will again bring back the tempdb "too large too soon". It seems a temporary fix only: I would suggest you to go through the similar post:
https://ask.sqlservercentral.com/questions/119120/tempdb-has-grown-to-1066-gb-how-to-bring-it-down.html [tempdb growth][1] to identify whats causing the tempdb to grow that big: if not then you can always: Use tempdb DBCC shrinkfile('tempdv', 1024)-- Note 1024 should be adjusted accordingly If above does not work then: use tempdb GO DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers DBCC FREESYSTEMCACHE ('ALL') -- clean system cache DBCC FREESESSIONCACHE -- clean session cache DBCC shrinkfile('tempdv', 1024)-- Note 1024 should be adjusted accordingly PS note: Clear cache on production servers at you're own risk as they are not suggested to do so. [1]:
https://ask.sqlservercentral.com/questions/119120/tempdb-has-grown-to-1066-gb-how-to-bring-it-down.html
19 People are following this question.
