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?


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.

  • Thanks, Brent (fixed your WhoIsActive link, btw). Based on this result, it certainly appears to be those big nested views. – BradC Oct 27 '11 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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