Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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