Removing a BI Extent

[doom1701]

Had our database shutdown in the middle of the night last night; our variable length BI extent filled up. Not sure if it was the right thing to do, but I just added another bi extent.

I believe the problem was caused by a piece of code that we run quarterly; it is a big process, and this is the first time we've run it after updating to the newest rev of our system. So I am going to research that and make sure the problem doesn't occur again, but I really hate having that 2GB bi extent just sitting out there. It probably doesn't matter, since BI is on it's own disk pair and I never come anywhere close to filling it...but I'm a neat freak I guess.

I'm thinking just truncate bi, do an offline backup, delete and rebuild the database (with my desired BI structure) and restore the database. Am I on the right track? And is there something different I should have done to recover?

 

[TomBascom]

You want to use "prostrct remove dbname bi" after truncating rather than simply blowing it away with "rm" or "delete" or a mouse click.

 

[doom1701]

So, just so I know what I'm doing, after I truncate the BI this weekend, do I then run

prostrct remove <dbname> bi

and that's it?

From what I can garner from the online documentation, do I probably need to feed it the extent name as well?

I'm guessing I still need to do something to change the structure, but if I can do it without rebuilding the database that would be great. I'm just not clear on the exact steps.

 

[TomBascom]

You do not need to provide the extent name. Prostrct remove removes the last empty extent. You run it multiple times to remove mulitple empty extents.

Whenever I'm planning to do something like this that I have not done recently or that I am unsure of I make a point out of testing it on a copy of the "sports" database first. It save a lot of embarrassment. It might even save your job