Category: Geeky/Programming

Recently, have been running into space issues from a content database perspective in SharePoint (MOSS) 2007. The DB server is still running SQL 2000 on a semi-old box, but since there are physical hard drives, hard to extend without a lot of surgery. Anyways, did some digging and found some things that may be useful for others..

1. You need to be Site Collection Admin and “God” on the servers.

I thought I was, but was mistaken. Was set up a while ago by consultant and I wasn’t involved, and added later. I had some pretty good rights, but not what I needed. I had Central Admin “admin” and other but not on the root site. Also some of the domain users that have rights I don’t have passwords for anywhere as an oversight, so kind of at a loss. I had to get that first before moving forward. I found a solution on this blog. I ran the following from the command line on the central admin server for my site and domain and got the access I needed to run what I needed to run.

stsadm -o siteowner -url http://sharepoint.test.com/sites/[sitename] -ownerlogin [DOMAINnetID]

2. I ran Quest’s free server reporting tool.

This is pretty cool, you can go here: http://sar.ondemand.quest.com and install a little WCF service and then run the reports right from your browser, you just have to have the admin rights to the SharePoint site collection to do it. I ran this and found only around 5 GB of usage, but my database was at 222 GB with 278 MB free space not utilized.

After digging around, I found the issue. First, the recycle bin in SharePoint was full, but also, the “Second Stage Recycle Bin” was ultra full. and Ultra being 217 GB full. What is the second stage recycle bin? Well, users can delete from the site, and it basically changes a db flag to hide the file, then, after 30 days (by default) it moves it to the 2nd stage recycle bin. There it sits and sits and sits. You need to clean this one up, but if you don’t you end up in a situation where I am at.

You can’t turn off the 2nd Stage Recycle Bin because there is too much stuff in it!

3. Delete Items from the 2nd Stage Recycle Bin using PowerShell.

I found this cool PowerShell script here and modified it. First I changed the rowlimit to 10k, and I added a date variable to output the date so I could see that progress was being made. I also am checking my DB size as I run it and watching the free space go up, up up!

#################################################
#
# flushrecyclebin.ps1
#
# Invoke this from a Powershell prompt by calling "./flushrecyclebin.ps1 http://webapp/siteurl".
# This script will only delete items in the second stage (site collection) recycle bin, so you
# will need to manually flush items you wish to delete from the first stage into the second
# stage.
#
#################################################

# we accept this parameter on the command line. If you have several sites' recycle bins to
# flush, you could easily turn this into a foreach loop
param($param_site);

# set this to be an acceptable number of records to delete in one batch. If your recycle bin
# has more items in it than this, you will need to rerun it
$rowlimit = 10000;

#################################################
#
# Don't change below here
#
#################################################

[void] [System.Reflection.Assembly]::Load(”Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

$site = new-object Microsoft.SharePoint.SPSite("$param_site");

$query = new-object Microsoft.SharePoint.SPRecycleBinQuery;
$query.ItemState = "SecondStageRecycleBin";
$query.RowLimit = $rowlimit;

$itemcoll = $site.GetRecycleBinItems($query);
foreach ($item in $itemcoll) {
    $a = Get-Date
    $id = $item.ID;
    write-host -nonewline $a " ... Deleting .. " $item.Title " ... ";
    $itemcoll.Delete($id);
    write-host "Done";
}

So where does this leave you? Well, first, on a new SharePoint 2007 setup you want to setup the Recycle Bin Settings so you don’t get caught like this. If you go to Central Administration > Application Management > Web Application General Settings and at the bottom you will see the Recycle Bin settings:

Like I said, with a full 2nd Stage Recycle Bin, you are stuck and can’t really do much here. But once clean, you probably want to reduce the number of days to like 7 and also possibly turn off the 2nd stage Recycle bin or reduce the size.

After cleanup like I did, you will want your DBA to probably shrink the file, this is one of the times that shrinking makes sense.

Happy SharePoint’ing!