Brawndo,
Assuming you don't have basic configuration issues with your TempDB, i.e. too small with not enough auto-expand, insufficient storage on the hosting volume, not enough files per the recommended configuration, sufficiently fast storage to handle you workload, etc., you could have one of two types of problems.
First, and this is arguably the more common, you could simply be generating a temporary table of such size that no amount of TempDB could do the job. For example, a ledger transactions SSRS report run wide open against hundreds of millions of transactions is just going off the rails, plain and simple. The statement that "crashed" may not be the statement ultimately responsible. In other words, the error message may indicate one statement failed to find the space it needed, but the problem is another statement entirely that consumed, or more likely is still consuming, the TempDB space, and when the transaction is terminated a bunch of space is freed relatively instantly. If your TempDB is really filling up in this way, with actual data/index/log pages in temporary tables, you should be able to monitor this easily with built-in Windows performance logging and see it on Disk used reports against TempDB.
Second, and this is probably less common but far more insidious, you could be suffering from a version store issue due to a long running transaction, even if that transaction is read-only. If you're not familiar with the version store and how TempDB is used to support the version store, take an hour and do some reading. In particular, note that the version store does not make TempDB appear full on Disk usage reports. Also, depending on your system throughput, the version store can consume a truly amazing amount of storage in a very short time.
Just to give you some numbers. I have a production instance with 64 GB of TempDB sitting on 100 GB dedicated volume, and our system idles at about 2,000 Batch requests/second with spikes to 30,000. On a normal day, TempDB never exceeds 5% full according to SQL Server metrics, and never auto-expands. However, we went through a bad spell a year ago where TempDB would fill, expand to fill the dedicated volume, and bring everything crumbling down, over the course of about 45 minutes. That's 0 GB to 100 GB in under an hour, which was much faster than any of our drive space alerting software was checking for, and so it kept blindsiding me.
The problem was the version store and long running transactions. The version store can be monitored with DMV's, and long running transactions are perhaps even easier to monitor, and an open transaction older than say 5 or 10 minutes may deserve your attention, though a few may be normal too (large journal postings, etc.).
In any case, this is a side of TempDB that you don't hear or read about very often, and particularly not in response to questions like yours even though it's highly relevant.
Good luck and hope this helps!
Share
Report
All responses (
Answers (
17,786
