-1

how I can shrink tempdb log file when reaching a specific size?? I'm scheduling job to shrink tempdb but still I face a problem of disk space due to tempdb is full!

closed as too broad by Philᵀᴹ, Marco, mustaccio, Max Vernon, Paul White says GoFundMonica Jun 28 '18 at 12:21

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    If your tempdb is full you can't shrink it anyway. You'd better consider growing of disk space for tempdb and refactoring of tasks which use it so highly – Denis Rubashkin Jun 27 '18 at 7:26
  • Shrinking the file probably won’t gain you much as there are one or more statements executing that is either using temp tables or some indexing operation that is using the tempdb for sorting which is causing the growth. tempdbs recovery model is set to simple by default which as transactions complete the log space it consumed is marked for reuse. Shrinking the file on a heavy used tempdb could result in no space being recovered, time out or other running transactions fail. Look for the cause by tracking the growths over the course of a day to help in isolating what is causing it – Aaron Jun 27 '18 at 8:57
  • Shrinking the file is not the solution. It’s just going to grow again. Do you want to change your flat tire every morning or fix the leak you keep getting from the nails and glass all over your driveway? See dba.stackexchange.com/q/19870/1186 – Aaron Bertrand Jun 27 '18 at 12:30
2

Isn't it better to just accept that the requirement for your SQL Server is a certain disk space and make sure you have the budget to allocate that amount of space? Keep shrinking seems like a futile fight in the end. Makes me think of the old blog post I wrote a decade ago about file shrinking and leaking roof: http://sqlblog.karaszi.com/leaking-roof-and-file-shrinking/. Anyhow, you could crate an SQL Server Agent alert for this based on a performance monitor counter, that kicks off an Agent job to do the shrink. But again, I wouldn't do it that way.

1

Yes, I agreed to what others have posted where you should consider increasing your disk space for your tempDB, but I am more inclined to another approach as it feels more of finding the cause to the increasing tempDB log.

That is, to identify the query that consume most of your tempDB log and its frequency. Depending on the type of query, you can have different approaches to it.

For example, if the high tempDB log is caused by a user process. Feedback to the user/application team and see if the query could be further improved and the criticality of the query. From here, you can choose to write a SQL job to kill the highest log consumption query whenever the tempDB log reach to a certain threshold (and that, you probably have to disable auto-growth). Data size can also played in a part (though it depends on what type of query is running), so reducing could help as well.

Only when the query has been optimized but yet it requires a certain amount of space, then do proceed to request for more disk space allowance for your tempDB.

Not the answer you're looking for? Browse other questions tagged or ask your own question.