Obliterate Performance - Custom Stored Procedure

dking513 · Post by **dking513** » Tue Oct 11, 2005 12:00 pm

Hello,
Like many other users, I have run into problems with the very slow performance of the 'Obliterate' function.

Reviewing the posts that have been made on this topic I saw a suggestion to use SQL to directly call the spobliteratefsobject stored procedure. The only problem with this is that it requires that you know the correct arguments to pass. One user was getting this information by running a profiler trace on the obliterate attempt made with the GUI. This seemed very cumbersome to me, as I have a large number of items I want to obliterate.

I wrote the following stored procedure, which reads information for all deleted items from the tbldeletions table into a cursor, and then iterates through the list calling the obliterate stored procedure.

I have found that this performance is much better than through the GUI.
I can check on my progress by the sql statement: select count(*) FROM tbldeletions with (nolock). The count goes down as items are obliterated. I am using a nolock hint so that I don't try to compete with whatever the obliterate stored procedure is doing.

I'm posting this here, because others might find it helpful.
Also, it would be nice to get some feedback from SourceGear to make sure that this does indeed do the right thing, and doesn't orphan any records anywhere.

<SNIP/>

ian_sg · Post by **ian_sg** » Wed Oct 12, 2005 8:31 am

This is definitely not safe. Don't do this. The primary reason it's faster is also the primary reason it's dangerous: there's no SQL transaction. If the obliterate can't complete for any reason (like a branch conflict), you'll be leaving your repository in a half-baked state.

There are a few other, somewhat complicated reasons why this isn't safe. I'll summarize by saying that by the time you made this script "safe", you'd be doing exactly what the admin client does, and you'd have the same performance characteristics.

I'm actually going to remove the script from your post, dking, just to remove the temptation for people to use this.

dking513 · Post by **dking513** » Wed Oct 12, 2005 8:35 am

Ian,
You suggested using this stored procedure to another user who was having problems.

See thread:
http://support.sourcegear.com/viewtopic ... obliterate

That's where I got the idea. If you want to remove my post so as to avoid tempting users, you should remove the other posting as well.

-Dave

ian_sg · Post by **ian_sg** » Wed Oct 12, 2005 9:01 am

Good point. I have removed it to be consistent.

I probably didn't explain sufficiently in that thread that this is more of a last-ditch step than a good idea for casual use.

The situation in the other thread was different in several important ways. Vault was effectively non-functional due to a full disk. We were obliterating only data that could be re-imported from VSS. We knew the data being removed was so big that an obliterate from the GUI client would never have worked. And in the end, it didn't work out and the user had to re-import from VSS anyway.

Anyway, sorry for not being clearer about this. Rest assured that we know obliterate's (lack of) speed is a pain for some folks, and we continue to work on improving it without compromising its safety.

dking513 · Post by **dking513** » Wed Oct 12, 2005 9:14 am

Thanks for the clarification Ian.
Unfortunately, I'm in a similar position to the other user. I need to obliterate a large amount of data (Gigs) and haven't been successful through the GUI. I'm not sure what else to do. If there was some way to export files (with their history) from one database to another, that would do the trick, but I know that this is also something that you are working on, but isn't ready yet. I guess just add one more vote for the "export" functional enhancement for some future version.
Thanks,
Dave