twice in the last week the tempdb LOG has filled it's disk. i've never had an issue with the LOG file for tempdb, it's always been sized quite small for all of our servers. we recently had it go from 10GB to 30GB (filled the disk). we extended the disk to 60GB, and today it filled it again. we have identified the offending application (it's on a shared server), killed the job, and the job will not be run again until the developers can debug what is going on. at the moment, the temp log file (82GB) larger than tempdb data file (72GB).
i'm just wondering - is there any bug or a special circumstance that would cause the tempdb LOG would be used so heavily? it's always been small and lightly used, even on our largest and most heavily used servers.
In most such cases, you'll find that you have one or more queries that have "accidental cross-joins" in the form of many-to-many joins and is normally caused by a misunderstanding of the data leading to insufficient join criteria. You'll need to find those and fix them. Once found and if the design of the database doesn't support something more logical, then you'll need to use "Divide'n'Conquer" methods to break the query up, possibly storing 1 or more interim results in a Temp Table which, ironically, will reduce the Temp Table usage.
Sometimes such queries are born of the idea of a "load everything now" mentality (I think they're called "Aggressive Loads"). They usually come in the form of large queries with lots of joins. I've seen up to 40 joins and I know others that have seen 80.
Some of these types of queries are pretty easy to find... just look for the ones that have DISTINCT in them. 😉
The most effective thing that you could do is help the developers achieve their goal without crushing the server. Help them rewrite the code and do it without arrogance. They'll love you for it. As David Poole once said about being an exceptional DBA, "If you're the first person people seek out for database problems rather than the last, you might be an exceptional DBA". I sit smack dab in the middle of the Developer to make that an easy thing for them to do and it has worked wonders for them and the code. In the long run, it has also worked wonders for me because there are much fewer (like none) system slowdowns that affect the end users and the SLA screen return times haven't just been met but have been seriously beaten.