How much available space do I need to create an index

[bugulgad]

I attempted to create a huge index on a view and it failed after 16 hours. I expect the indexed view to be about 200GB and I had about 300GB available.

It failed because the DB ran out of space on the drive, autogrowth is set to ON.

This is SQL 2005.

Is it true you need twice the space for the creation?

Anything I can do to make the create index script less costly in space. This is not a rebuild either......there is no index there to begin with.

 

[gmmastros]

What is your auto grow setting? Is it a percent, or is it a fixed size? Either way, could it be that it tried to allocate more space than you have left?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bugulgad]

Oh yes, it definitely auto grew until the Data file ran out of space.

BUT, the process also had TempDB data up to 230GB and that is on the same drive.

So, I guess I am wondering if the index creation can be made to not use so much TempDB?