SQL 2012 tempdb size

Good Day,

We are seeing an issue with our SQL 2012 tempdb folder reaching enormous sizes (691GB) which in turn we are relating to the server maxing the available RAM. Performance degraded to the point of production machinery dropping connections to the SQL server due to timeouts.

I was able to reboot the server which dumped the cache I'm guessing and everything is working properly for now.

How can we identify what is causing the tempdb to grow so big?

How do we eliminate this issue from happening again?

By default, is there not a default aging for the data in the tempdb?

Thanks,

David
BrockstedtAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I was able to reboot the server which dumped the cache I'm guessing and everything is working properly for now.
That's because every time you restart the SQL Server service (no need for reboot server) the tempdb is recreated.

How can we identify what is causing the tempdb to grow so big?
Check the running processes when you see that tempdb is growing fast.

How do we eliminate this issue from happening again?
Only after you identify the issue you can eliminate it.

Just want to give you the two more common tasks that can make a tempdb grows:
Reindex or Reorg jobs
Working with massive data (delete, insert or update)
BrockstedtAuthor Commented:
Thank you for the response.

Is there a way we can limit the size of the tempdb growth. If so, how can it affect the performance of the production database?

From monitoring as much as I was able to today, it appears this may be related to some report queries. Not 100% sure yet, but looking into it
Vitor MontalvãoMSSQL Senior EngineerCommented:
Is there a way we can limit the size of the tempdb growth. If so, how can it affect the performance of the production database?
Yes. In SSMS, right-click on tempdb database and select Properties, then Files. Now you configure the Autogrowth for each tempdb file. I would suggest that at minimum you should keep the tempdb with a size that can handle the largest table of your databases.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.