04 November 2010

c# script to delete very large second stage recycle bin items in sharepoint 2007

In SharePoint 2007, when item versioning is set to no limit for a document library, you could easily have a spiraling content database size.  When you try delete a ginormous item, it'll eventually get stuck in the second stage recycle bin, and you'll never be able to reclaim that database space or file space - a forced purging of the recycle bins through a UI operation will time out eventually, rolling back a huge transaction, probably freezing the site for your users - cue fire and brimstone.

The solution is to run the following script off-hours.  Off hours helps minimize any potential blocks SQL server may issue against database pages as it goes around deleting the row content from alldocversions - from what I can tell, many I/O Page Latch locks are issued when SQL is deleting blobs, so to be nice to this script and run it off hours.  Compile it into a c# console application referencing the sharepoint dll. There isn't much for progress reporting included in the script, but if you can figure out how many rows you are removing from alldocversions, you can keep issuing the t-sql command 'sp_helpdb alldocversions' to keep a tab on the progress.


using System;
using Microsoft.SharePoint;

namespace DeleteRecycleBin
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Enter Site Url (example: http://site) and press enter.");

            string siteUrl = Console.ReadLine();

            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite(siteUrl))
                    {
                        //SPRecycleBinQuery recycleQuery = new SPRecycleBinQuery();
                        //recycleQuery.ItemState = SPRecycleBinItemState.SecondStageRecycleBin;
                        //recycleQuery.OrderBy = SPRecycleBinOrderBy.Default;

                        //SPRecycleBinItemCollection recycledItems = site.GetRecycleBinItems(recycleQuery);
                        //int count = recycledItems.Count;

                        SPRecycleBinItemCollection recycledItems = site.RecycleBin;

                        int count = recycledItems.Count;
                        while (count-- > 0)
                        {
                            recycledItems.Delete(new Guid[] { recycledItems[count].ID });
                        }

                        Console.WriteLine("All items deleted.");
                    }
                });
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex);
                Console.WriteLine("");
                Console.WriteLine("Press enter to continue.");
                Console.ReadLine();
            }
        }
    }
}