Recoup and reclaim SharePoint 2013 SQL content database space from a deleted site collection

Recoup and reclaim SharePoint 2013 SQL content database space from a deleted site collection

With the introduction of the site collection recycle bin (back in SharePoint 2010), it takes a little more work to reclaim the hard drive space of a large SharePoint 2013 content database from which you have have deleted a large site collection from.  You need to use a combination of PowerShell commands, starting a timer job and shrinking the SQL database to accomplish this. 

If you don’t want to wait for the automatic recycle bin purging, the first step you need to do is permanently remove the site collection from the site collection recycle bin (ensure you have a backup if this is mission critical).  From the SharePoint Management Shell as Administrator, run the following which will give you the list of deleted site collections.:

Get-SPDeletedSite

At this point you will get a list of the deleted site collections:

scdelete1.png 

Once you have done this, copy the SiteId of the site collection you want to delete and then run the following command which will queue the site collection to be deleted by the Gradual Site Delete timer job for that web application:

Remove-SPDeletedSite ‘5a4a1095-c412-480a-9b42-84e30835771f’

scdelete2.png 

Since deleting site collections can delete an enormous number of rows of data in your SQL database, which in turn can cause SQL Server database row and table locks, it does them through a web application Gradual Site Delete timer job which deletes the SQL DB row items in batches of 1000 rows (read more on Bill Baer’s post here).  If you don’t want to wait for the daily timer job to run that starts this job, to invoke this job you can either go through the SharePoint Central Admin Timer Job GUI and start for your web application or just run this PowerShell command through SharePoint Management Shell as administrator:

Get-SPTimerJob “Gradual Site Delete” -WebApplication http://<YOURWEBAPP> | Start-SPTimerJob

scdelete3.png 

scdelete4.png 

Note: This job may take quite a while to run depending on the size of your content database.

The last step is to manually shrink your database and log files in SQL Server for that content database (although the DBA’s might not be so happy about it).  You can do this via the GUI or SQL Commands (listed below).   Right click on database –> Tasks –> Shrink –> Files

scdelete5.png 

One more thing, you may need to go to your database file properties in SQL and decrease the initial size of the database if it was ever set:

scdelete6.png 

And voila, the site has been permanently deleted and you have got your storage space back!

 
The guidance from Microsoft for shrinking files in SharePoint 2010 (and I’m pretty sure still applies to SharePoint 2013) can be seen here: http://technet.microsoft.com/en-us/library/hh133453(v=office.14).aspx#DBMaintenanceForSPS2010_ShrinkingDataFiles.
 
Here are some excerpts from Microsoft’s guidance on shrinking SharePoint content db files:

“Only perform shrink operations rarely, and only after you perform an operation that removes a lot of data from a database, and then only when you do not expect to use that free space again. Data file shrink operations cause heavy index fragmentation and are extremely resource-intensive”

“However, if you must shrink a database, use the following guidelines:

  • Do not auto-shrink databases or configure a maintenance plan that programmatically shrinks your databases.
  • Shrink a database only when users or administrators remove 50% or more of the content and you do not expect to reuse the unused space.
  • Shrink only content databases. Users and administrators do not delete enough data from the configuration database, Central Administration content database, and various service application databases to contain significant free space.
  • Shrinking databases is an extremely resource-intensive operation. Therefore, if you absolutely must shrink a database, carefully consider when you schedule the shrink operation.
  • After you shrink a database, the indexes in that database are fragmented. Use ALTER INDEX… REORGANIZE to address the fragmentation. If you are not configured to allow instant file initialization, shrink the database to a target size that accommodates the size required for the near-term growth that you expect. For more information, see Database File Initialization.

You can shrink databases and database files manually to recover space by executing the DBCC SHRINKFILE and DBCC SHRINKDATABASE statements in SQL Server 2008 or SQL Server 2005 Management Studio.

 

Leave a Reply

Your email address will not be published. Required fields are marked *