I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly.
The cube rebuilds have been using more and more of the TempDB on the SQL server each night. Last night TempDB data files grew to about 100GB before using up all space on the TempDB drive.
I can add more space, but I'd feel better if I understood more about what is going on here:
- How/why do cube rebuilds use TempDB space on the SQL server?
- Is there any way to reduce the amount of TempDB that it uses, or force it to "release" tempdb between steps, or something?
- Is there any way for me to estimate the amount of space it might continue to use as my data continues to grow? (based on the size of certain key tables in my database, or something?)
Edit: Only significant difference in this particular database that I can think of is that they use some very large, very complicated nested views for their final data set. If these complex views are the ones being used to build the cube, could that explain the high tempDB use?
Best Answer
While the cube builds, you can run Adam Machanic's sp_WhoIsActive diagnostic tool to see which queries are allocating space in TempDB. I recorded an sp_WhoIsActive tutorial video to show how it works. Include the @get_plans = 1 parameter when you call it, and you'll also get the execution plans. That way you can see exactly what's using TempDB and why.