Cleaning up large content databases and reclaiming unused disk space

Dealing with large SharePoint content databases can be a daunting task. Microsoft has the recommendation to keep your content databases below the 200GB mark for databases which are used daily. But when you are dealing with backup/restore, migrations and general operations which involve moving around those kind of databases, even 200GB can be a huge pain in the ass and will cost you in terms of time you are spending looking at a progress bar or watching a percentage creeping slowly to 100%.

A solution for this is to split up the content database into smaller databases provided that you have multiple site collections in that database which can be moved out.
Relocating a site collection to a different content database is very easy. You can do this with the Move-SPSite cmdlet.

Once you moved out a site collection to a different database, you will notice that your huge database is not getting smaller. That’s because the site collection which has been moved, is still in that database but it’s marked for deletion.
The actual removal of that site collection is done by the Gradual Site Delete timer job. This job runs once a day. Once it has run, the site collection is completely removed from the database.
But still, if you look at the database, it will not be any smaller than before. When you look at the used space in the database, you will see that this has decreased and the unused space has grown. The unused space is not released.
To release unused space, … *ducks for cover* … you can shrink the database. There, I said it.

Generally speaking, shrinking content databases is not done. You can do this, but it has no advantages and it has a negative effect on performance because when someone adds something to a site in that database, it has to expand to be able to store anything.
So, shrinking is definitely something you should avoid at any cost… except for the case where you have such a huge content database that you’ve split up into smaller content databases. The reason for splitting up the database in the first place was to make it smaller, right? To have a size which is much more manageable. But in order to get it back to a reasonable size, you need to shrink it. There’s no way around it.

During a migration from SharePoint 2007 to SharePoint 2013, I had to migrate a content database of 220GB. All things considered, this is not huge. It’s large, but not huge. This content database contained around 20 site collections. Backup of this database was not an issue… 20 minutes to backup this database. Copying this backup to a SharePoint 2010 migration server was frustrating. It took over an hour. Yeah, It SHOULD go faster but if you pass through a 10Mbit router, you are copying at USB 2.0 speed! But this was nothing compared to the time the Mount-SPContentDatabase cmdlet needed to complete to attach this database to the web application and do the actual upgrade from SP2007 to SP2010. This attach/upgrade took almost 3 hours and then it just aborted due to lack of disk space. The migration server had a data disk of 600GB and it just filled completely with the transaction log that was created as part of the attach/upgrade process. So, I lost 3 hours, had to wait until extra disk space was added and restart the whole thing again. By the time it had attached and upgraded, the size of the database was actually increased to 330GB.

When everything was attached and upgraded, I decided that I’m not going through this again when I do the migration from SP2010 to SP2013. I needed to have databases which are easier to handle. So, I split up this database into 5 databases of which the largest was still 115GB. But ok, nothing I could do about that in the short term.

Running the Gradual Site Delete job however proved to be a pain as well… took almost 6 hours to complete! Started around 2PM. Went home at 5PM. Next day, I noticed it finished around 8PM. So, I started the shrink operation of the database… lost half a day with that. Wasn’t able to do anything with that database for the larger part of the day.

Since this was only a “test” migration, I realised that history was going to repeat itself during the final migration and that I needed a way to make use of those lost hours between the finishing of the gradual site delete and the shrink. When the gradual site delete is done, start with the shrinking to have it done in the morning.

Enter… PowerShell!

The script below is going to kick off the Gradual Site Delete timer job for a specific web application and it then will monitor this job every 5 minutes to see if has completed. Once it has completed, it will continue with the shrinking of the database. The shrinking happens in 2 stages. The first stage is done with NOTRUNCATE. This means that SQL will move all pages to the front of the file but it will not reclaim unused space. The size stays the same. The next step is a shrink operation with TRUNCATEONLY. This will just remove all unused space from the end of the file. It’s basically the same thing that happens when you do a Shrink Database from the Management Studio.

Again, don’t do this as a part of a weekly maintenance routine because the first step of the shrink will introduce index fragmentation in your database. Seriously! For me, this was a necessary cleanup I had to do as part of a migration project to reorganize the content database and minimize the migration time! The environment I was doing this in, was a intermediate SharePoint 2010 environment, not a live environment.

Also, the Shrink operation in the script allows you to specify a percentage of free space it should reserve for unused space.


I used 5%. This way, for a content database of 100GB, 5GB of free space is retained. You can change this if you want, or you can add an additional parameter which allows you to specify the amount of free space it should keep.

    Cleanup site collection marked for deletion and perform a shrink

    Cleanup site collection marked for deletion and perform a shrink.

    File Name: CleanUp-ContentDatabase.ps1
    Author   : Bart Kuppens
    Version  : 1.0
.PARAMETER WebApplication
    Specifies the URL of the webapplication where the cleanup has to be executed.
.PARAMETER ContentDatabase
    Specifies the name of the content database which has to be cleaned.
    PS C:\> .\Cleanup-ContentDatabase.ps1 -WebApplication http://teamsites.westeros.local ContentDatabase "SHP_WST_Content_TeamSites"
# Load the SharePoint PowerShell snapin if needed
if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null)
    Write-Output "Loading SharePoint Snap-in..."
    Add-PSSnapin Microsoft.SharePoint.PowerShell

# Check if the webapplication exists
$webapp = Get-SPWebApplication $WebApplication -ErrorAction SilentlyContinue
if ($webapp -eq $null)
    Write-Host -ForegroundColor Red "Web application '$WebApplication' doesn't exist, halting execution!"

# Check if the contentdatabase exists
$contentdb = Get-SPContentDatabase -Identity $ContentDatabase -ErrorAction SilentlyContinue
if ($contentdb -eq $null)
    Write-Host -ForegroundColor Red "Content database '$ContentDatabase' doesn't exist, halting execution!"
    # Check if the content database is attached to the web application
    if ($contentdb.WebApplication -ne $webapp)
        Write-Host -ForegroundColor Red "Content database '$ContentDatabase' is not attached to '$WebApplication', halting execution!"

# Start the Gradual Site Delete for the specified web application
$start = (Get-Date).ToUniversalTime()
$timerjob = Get-SPTimerJob -Identity "job-site-deletion" -WebApplication $webapp
Write-Host "Starting 'Gradual Site Delete' for $webApplication..."
Start-SPTimerJob -Identity $timerjob

# Wait for the job to complete
Write-Host -NoNewLine "Waiting for job completion on database $ContentDatabase..."
$jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}
while ($jobhistoryentries -eq $null)
    Start-Sleep -Seconds 300
    $jobhistoryentries = $timerjob.HistoryEntries | ? {$_.DatabaseName -eq $ContentDatabase -and $_.EndTime -gt $start}
Write-Host " Completed with status : $($jobhistoryentries.status)"

if ($jobhistoryentries.status -eq [Microsoft.SharePoint.Administration.SPRunningJobStatus]::Succeeded)
    Write-Host "Continuing with database Shrink..."
    [system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") >> $null
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $contentdb.Server
    $db = $server.Databases[$ContentDatabase]
    Write-Host "Current size (Mb) : $($db.Size)"
    Write-Host "Shrinking Step 1/2..."
    Write-Host "Shrinking Step 2/2..."
    Write-Host "New size (Mb)     : $($db.Size)"
    Write-Host "The timer job failed. Halting execution!"

You can find this script in my PowerShell repository on GitHub.

Categorized as SharePoint

By Bart

Bart is a certified SharePoint consultant / architect at CTG Belgium NV with a broad professional experience in IT, a background in software development with a specialisation in Microsoft products and technologies and a solid knowledge and experience in Microsoft SharePoint Products and Technologies. He started as a COBOL developer on a mainframe environment and grew into software development for Windows platforms. Participated in projects varying from migrations of existing applications to development of Web applications and Windows applications. Became fascinated by the SharePoint 2007 platform and strongly believed in the added business value of this platform. Is since then fully committed to SharePoint and focuses on SharePoint implementations, migrations, integrations, design and coaching. Stays on top of new developments within the SharePoint technology stack and related technologies.

%d bloggers like this: