Delete All versions from a Document Library in SharePoint 2010

The issue we had was the database size was growing at alarming rate which caused some serious concerns over SAN allocation.  The Size of Site was 140 GB and Document Library (Document Set) was 100 GB. We figured out based on a historical analysis of DB growth , there was an increase of  60 GB in 2-3 weeks of time for a sub site which was alarming.

This happened as versioning was enabled but no major versioning limit was set. That allows too many versions to be created in SharePoint based on the software boundaries. A bespoke application housing LOB Process and a bespoke timer solution somehow created too many versions as the limit was not set.

Run this Power Shell Command to change the number of version to be retained:

get-spWeb http://portal.contoso.com/Sites/Test/Sub-Test

ForEach-Object {ForEach($_.list in $_.Lists){If($_.EnableVersioning -eq $True){if($_.Title -eq "Shared Documents"){Write-host "List:"$_.Title "- List ID"$_.Id;$_.MajorVersionLimit = 4;$_.Update();ForEach($_.item in

$_.items){$_.item.URL;$_.update()}}}}}

Replace the URL with your Site URL and Shared Documents with the Document Library  Name you want

Change MajorVersionLimit to the number you require in my case I changed it to 4.

When that’s done, Connect to the corresponding Database Server via SQL Server Management Studio

Shrink the database by re-organizing the indexes, refer to DB maintenance guide before performing this on technet.

This released around 110 GB and we now have the Site Collection Size as 30 GB.

The Storage Metrics was showing incorrect numbers as We had June 2011 , August 2011 CU resolves this issue but the file size on Database was correct

Run this in Power Shell to get the size of all Site Collections to verify the numbers

Get-SPSite | select url, @{label="Size in MB";Expression={$_.usage.storage/1MB}} | Sort-Object -Descending -Property "Size in MB" | ConvertTo-Html -title "Site Collections sort by size" | Set-Content SiteCollection.html

Thanks to Todd and some other references this helped immensely, saved my day. 

Important part of governance is to define versioning , though we have the versioning defined on all other site collections, for this one it wasn't as the necessity was due to bespoke application, timer job , but this has been changed now after experiencing this issue. Now we have defined versioning on this site collection too.