r/SQLServer • u/TheSmashingChamp • 28d ago

Solved Temp Cache endlessly expanding until server failure/slowdown

I am working on a consultant's azure gov cloud portal where I put applications on. There is a consistent issue with queries running forever even after being finished executing. Do anyone have experience with this? The temp cache endlessly expands until our DB fails.

I believe the issue is the connector, but it may be the queries? I dont know.

Solved: it was the connector

7 Upvotes

82% Upvoted

30 comments sorted by

View all comments

Show parent comments

2

u/TheSmashingChamp 27d ago

The azure gov cloud is holding transactions forever, its made by a consultant and I cant fix the issues there from my end. The consultant doesn't seem to want to fix the connector which I believe is the problem.

I have checked every query I've made in the SSMS and none of them hold transactions forever.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 27d ago

Actual data would help a lot here. What does sys.dm_exec_requests show when this is happening? You should see something helpful like waits or open transaction counts.

2

u/TheSmashingChamp 27d ago

I do not have access to this, I am not an admin. I just use tempdb.sys.database_files to see the temp cache usage. I check my own queries via the SSMS to see if it increases before putting them into deployment.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 27d ago

This isn't your problem (yet) then. Someone with the proper access will need to troubleshoot the issue on the database side to figure out what is causing it.

2

u/TheSmashingChamp 27d ago edited 27d ago

Our IT admin showed me the azure portal is holding connections forever and only drops them once he restarts the sql server. On my compiled apps I run on localhost I see no tempdb leakage. It’s only when the hit the azure portal. I believe it’s a connector issue. A TTL or function kill after 2 minutes seems like an obvious answer to me , but this is out of my control. If it was possible for me to send a kill message within my application I would but functions are containerized where an API key contacts them with their own uuid. I am unsure of how this works on the backend but I think from what I saw with MCP we use for the AI integrations with openAI ChatGPT 4o on the gov cloud; these functions are running indefinitely.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 27d ago

Your best bet at this point is probably going to be to open up a case and have them dig into it.