question

vivekchandra09 avatar image
vivekchandra09 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.
tempdbshrinkapproach
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
2 Likes 2 ·
thanks everyone. I was able to reclaim 129GB of tempdb space. Still trying to figure what causes the rapid growth.
1 Like 1 ·
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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Did you solve your issue?
0 Likes 0 ·
MAXKA avatar image
MAXKA answered
@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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MAXKA, thank you for the PS but I don't understand how clean caches will help with the TempDB growing? Could you please clarify your answer?
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.