asked
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.
@Mohamed4053 You should be able to get the information you need from an Extended Events session which will be much less impact on your server performance.
Why would you shrink your tempdb if it´s getting back to the 200GB you´ve mentioned? If you have the diskspace, leave it at 200GB.
Dear Kev, Thanks for your answer. This issue is happen Periodically (i.e happen on 7 days or 10 days or 15 days). So i can't run profiler daily. I believe that will give some performance
issues.Is there other way to capture the transaction/query that is causing tempdb to grow. Thanks in advance.
I would absolutely leave the tempdb size alone until you identify the offending query and address the issues. It needs to be that big to accommodate the work.
@fatherJack - Thanks for your comments. I am not much familiar with DBA activities. I was working as a developer. recently i am started working as DBA. If you provide some Example or query to find out from Extended Events session would be fine. Thanks in advance...
14 People are following this question.
