Large SharePoint Lists and Second-stage Recycle Bin

What's that? I'm starting my Lync blog with a SharePoint entry? Well, in a word: yes. Since I happen to manage and wrestle with both environment, I run into daily issues with both. For your reading enjoyment, you will be subjected to my own twisting of space and time, multiple revisions where I am always the hero, and literary abuse to be envied by the most verbose of Woot's(r) writing staff.

A month ago, I started a day off with a call from a SQL DBA, asking me WHY one of my SharePoint databases was going nuts, consuming 20% CPU on the box, and just generally crapping all over the environment. Due to my own amazing powers of deduction (and a general idea about where people tend to do things perhaps not completely planned out or properly executed), I determined that there was a mail-enabled discussion board taking in ~4000 emails/hour.

First things first: Shut off email intake for the discussion board and tell the guy managing the devices sending the emails to see a proctologist and get his head examined.

Second things, well, second: Clean out the discussion board. Woah... Not so fast there, Turbo. You said it has 1.7 MILLION list items? Nobody will ever read those, so retaining them is just a waste. Deleting them at 1500 rows and 2 minute wait per delete would be... 37.77777 hours, if I don't take a break, go to the restroom, etc. While some people might be happy to log the bulk of an entire work week to mind-numbingly refreshing and deleting out of SharePoint, them people don't get employed as SharePoint administrators at my company.

As a loyal Microsoft Office Server Stack implementer(er), off to http://www.bing.com I go. Finding a good script to do what I want to do is hard, but you learn to hack things together. Eventually, I realize there isn't anything out there close to what I need. I corner my friendly PowerShell guru, and eventually we come up with this:

SCRIPT BLOCK:

param([string]$Url, [string]$List, [switch]$help)
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
function GetHelp()
{
$HelpText = @"
DESCRIPTION:
NAME: Empty-LargeSPList
Deletes all items in a large SharePoint List.
PARAMETERS:
-url        Url to SharePoint Site
-list       List name
SYNTAX:
Empty-LargeSPList -url http://moss -list BigItemsList
Deletes all items in a large list.
Empty-LargeSPList -help
Displays the help topic for the script
"@
$HelpText
}
function Empty-LargeSPList([string]$url, [string]$list)
{
$site = New-Object Microsoft.SharePoint.SPSite($url)
$web = $site.openweb()
$buildlisturl = $url + "/lists/" + $list
$buildlist = $web.getlist($buildlisturl)

$count = $buildlist.itemcount
Write-Host "Items in list: " $count
$x = [int]($count/20)
foreach($iteration in $x..0)
{
$listquery = New-Object Microsoft.sharepoint.spquery
$listquery.rowlimit = 20
$removinglistquery = $buildlist.GetItems($listquery)
Write-Host "Deleting iteration:" $iteration
 foreach($i in 19..0)
    {
        $removinglistquery.delete($i)
    }
}
}
if($help) { GetHelp; Continue }
if($url) { Empty-LargeSPList -url $url -list $list}

Wait. NO. Seriously? Crap. Yes, Virginia, all that stuff I deleted DID go into the recycle bin. Well, actually, it didn't, and that's a GOOD THING(tm). However, I find that the recycle bin is full because somebody's been cleaning up that list manually for a while. Now that it's all aging off, my timer jobs to clean up the recycle bin are falling all over themselves? Why yes. Yes they are.

Back to Bing, where I find a nice script. Problem was, it gives no feedback and only nukes 50 rows at a shot. That's OK, for I know how to " | Get-Member".

Still: Thanks to Kirk Evans for doing to leg work on the script:
http://blogs.msdn.com/b/kaevans/archive/2010/10/29/emptying-the-second-stage-recycle-bin-in-sharepoint-2007.aspx

param([string]$Url, [switch]$help)

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

function GetHelp()
{
$HelpText = @"

DESCRIPTION:
NAME: Remove-SPSiteSecondStageRecycleBin
Empties the second-stage recycle bin for a Microsoft.SharePoint.SPSite Collection

PARAMETERS:
-url Url to SharePoint Site Collection

SYNTAX:

Remove-SPSiteSecondStageRecycleBin -url http://moss

Empties the second stage recycle bin for the SiteCollection.

Remove-SPSiteSecondStageRecycleBin -help

Displays the help topic for the script

"@
$HelpText
}

function Remove-SPSiteSecondStageRecycleBin([string]$url)
{
$siteCollection = New-Object Microsoft.SharePoint.SPSite($url);

$recycleQuery = New-Object Microsoft.SharePoint.SPRecycleBinQuery;
$recycleQuery.ItemState = [Microsoft.SharePoint.SPRecycleBinItemState]::SecondStageRecycleBin;
$recycleQuery.OrderBy = [Microsoft.SharePoint.SPRecycleBinOrderBy]::Default;
$recycleQuery.rowlimit = 500000 $recycledItems = $siteCollection.GetRecycleBinItems($recycleQuery);

$count = $recycledItems.Count;
write-host "Items in recycle bin: " + $count

for($i = 0; $i -lt $count; $i++)
{
$g = New-Object System.Guid($recycledItems[$i].ID);
write-host "Deleting: " + $g
$recycledItems.Delete($g);
}

$siteCollection.Dispose()
}

if($help) { GetHelp; Continue }
if($url) { Remove-SPSiteSecondStageRecycleBin -url $url }

So, now my DBA is happy. My users notice a performance improvement in SharePoint, so they are happy. I didn't spend 37.77777 hours deleting things from a discussion board, and another 37.77777 hours deleting them from the recycle bin, so my boss sure ought to be happy.