0

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
Sascha S
  • 1
  • 2
    Your query isn't valid, order by comes after where. – Dale K Feb 15 '23 at 23:26
  • 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 Feb 15 '23 at 23:35

1 Answers1

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.

Gavin
  • 22
  • 2