Fixing a database with too many orphaned blobs

Hello again,

This time, I'm gonna tell you about an interresting problem we ran into this week.

We had a customers solution, that contained around 6GB worth of media items (I know that, because we just loaded them into the solution using serialization).

Things where working fine - we removed them a few time to test our import tool (using the little known dbbrowser gem in Sitecore), and finally we where ready to backup the database and move it to it's final destination.

Entering the folder on the MSSQL server, looking at the .mdf file, showed that the database was taking about 25GB of diskspace.....

So clearly something was wrong - so tried shrinking the database, but it only said around 200MB of free space in the database, so that's not gonna work.

The next thing to try, was running the database cleanup tool inside Sitecore, which ran for a while, and then bugged out with a timeout exception.
This however indicated that whatever it was doing, it seems to be doing it on quite alot of data.

It seems the problem is, that this tool fails if there is too many orphaned blobs in the database - orphaned blobs can happen in a few cases - like removing media items using the dbbrowser it seems. (Overwriting media folder using the package installer might also cause this).

Talked to a co-worker, which had this timeout problem before, and got a SQL script from Sitecore Support, that does what the cleanup tool does, but works better, since it is run from inside SQL Server Management Studio.

Tried running it, and after around 90 minutes, it was done, and the database could be shrinked down to 7GB - weee.

However, the script had one problem, it was just standing there running, not really posting any info on what it was doing, how far it had come etc.

So, I took the SQL script, made a small C# program that does the same thing, but gives a percent indicator for how far it has come, and how long it expects it to take to finish running.

The best part of this is, that I'm allowed to share it with you guys - so here it is, my little tool, that fixes the problem in a friendly way.

You are welcome to disassemble it, if you like me are unsure about just running code downloaded from the internet.