How to reduce the tempdb database during the busy workload with out restarting the SQL server.
I have configured my tempdb in different location(D:), It has around 200 GB. Sometimes the tempdb growth will reach 200GB. When i faced this issue as of now i asked some downtime from client and restart the sql server as a temporary solution. Is there any other possible way to reduce the tempdb without restarting the sql server. **Note :-** - Sql server is running in stand alone server. So i can't create a log or data files(Don't have space in D: as well as other drives ). - I can't shrink the database because continuously some queries are running in running. I need recommendation to resolve this issue with out restarting the sql server?
Identify the transaction/query that is causing tempdb to grow, and look at ways of reducing it's impact. In the extreme case that you can't do that, then the client needs to accept that the requirement of their system is to have more disk space to accommodate the tempdb growth. Apart from that it looks like you already know that the recommended methods are a server restart, or shrink without any other activity in tempdb.