Problems with TempDb on the SQL Server

Question

I got some problems with my SQL Server. Some external queries write into the Temp db and every 2-3 days it is full and we have to restart the SQL database. I got who is active on it. And also we can check monitor it over grafana. So I get a exact time when the query starts to write a lot of data into the temp db. Can someone give me a tip on how I can search for the user when I get the exact time?

select top 40 User_Account, start_date, tempdb_allocations
from Whoisactive
order by tempdb_allocation, desc
where start_date between ('15-02-2023 14:12:14.13' and  '15-02-2023 15:12:14.13')

User_Account	Start_Date	tempdb_allocations
kkarla1	15-02-2023 14:12:14.13	12
bbert2	11-02-2023 12:12:14.13	0
ubert5	15-02-2023 15:12:14.13	888889

You can include the sql_text column in your query to see what is the query or stored procedure used, and ask to the user or team to improve the query and to delete the temporary table after finishing the procedure — rafaelmi
– rafaelmi, Commented Feb 15, 2023 at 23:35

Gavin · Accepted Answer · 2023-02-16 04:48:44Z

1

I would add this as a comment but I don’t have the necessary reputation points.

At any rate - you might find this helpful.

https://dba.stackexchange.com/questions/182596/temp-tables-in-tempdb-are-not-cleaned-up-by-the-system

It isn’t without its own drawbacks but I think that if the alternative is restarting the server every 2 or 3 days this may be good enough.

It might also be helpful if you add some more details about the jobs that are blowing up your tempdb.

Is this problematic job calling your database once a day? Once a minute? More?

I ask because if it’s more like once a day then I think the answer in the link is more likely to be helpful.

answered Feb 16, 2023 at 4:48

Gavin

222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problems with TempDb on the SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related