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!
Asked
Active
Viewed 2,283 times
2 Answers
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.
Tibor Karaszi
- 18,269
- 2
- 17
- 30
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.
Yoshiaki
- 231
- 1
- 7