Shrink SharePoint Database

Today I ran out of free space on my lab, and surprisingly my configuration database was consuming about 1 GB; whilst, the real consumed space is about 200 MB. Digging deeper, I found that the SQL MDF file (data file) has a lot of free space, which I was in a bad need for. Whereas, checking the SQL Report (Disk Usage) was confirming this.

For reference on how to check the SharePoint database disk usage report, you can have a look on it via SQL Management Studio, as shown below:

Normally, shrinking the SQL LDF file (log) is not a big deal, as you can shrink it via (Tasks >> Shrink >> Files), as shown below

Usually, doing the above should do the trick, and you should not worry about anything afterwards. However, if you are shrinking the data file (MDF) then you have to wait!!! Let's first explain why could the MDF file grow with a huge amount of unused space?

The answer is here . SharePoint databases do not automatically shrink data files, although many activities create unused space in the database. Activities that can create unused space include running the Move-SPSite Windows PowerShell command, and deleting documents, document libraries, lists, list items, and sites. Although it's possible and totally supported to shrink the SharePoint MDF files, but 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. Therefore, it's not recommended to automate this operation by any mean (auto shrink SharePoint databases). Hence, as documented in the same article, if you must shrink a database, follow the below 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. (yes I am doing it on the configuration DB, because it's my lab :) )
• 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.

Hereafter, as I believe you are going to proceed with the database shrink (that's why you are reading this article, right? :) ), you have afterwards to rebuild your database index. In order to show you the shrunk database index, I am sharing with you my database index fragmentation after shirking it. For reference, I used to bellow SQL query to get the database index fragmentation:

 USE [SharePoint_Content];
GO
-- Find the average fragmentation percentage of all indexes
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'SharePoint_Content'), NULL, NULL, NULL, NULL) AS a
 JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id 
order by 'avg_fragmentation_in_percent' desc;
GO

N.B., change the above highlighted text with your database name

Running the above SQL query gave me the below results:

From the above, and as documented here, based on the index fragmentation, you should consider either reorganizing or rebuilding the database index, as listed below:

Thus, as shrinking the DB caused high Index fragmentation (about 90's in some tables), I will rebuild the index. Therefore, in order to rebuild the index, you can do this either using SharePoint Health Analyzer rule, a SQL query, or using a SQL Maintenance plan.

N.B., for more information about the SharePoint jobs that is responsible for the SharePoint databases index defragmentation, please have a look on this article

Honestly, I would prefer using the SQL Maintenance plan than the SharePoint health analyzer rule, in case the index defragmentation fails for any reason...

• Open SQL Management Studio.
• Right-click Maintenance Plans, and select Maintenance Plan Wizard.

• Click Next.

• Give a name for your maintenance plan, and click Next.

• Tick the option "Rebuild Index", and click on Next.

• Click Next.

• Select These databases option, tick your database, which you already shrank, and then click on OK.

• Change the Free space options, enter value 80, and then tick the option "Sort results in tempdb", as shown below:

N.B., for more information about the above settings, you can have a look on this article.

• Click Next.

• Click Finish.

• Once the maintenance plan is created successfully, execute it, as shown below:

After performing the above steps, I ran the same SQL query to retrieve the index fragmentation, and found it completely got enhanced.