Can i Shrink SharePoint Content database Data file in production environment?

Question

Dear All,

We have one site collection in the production environment, In that site collection we have several lists and document libraries. Total size of the content database is 167 GB and available free space is 88.4 GB (52% free space).

we are using SharePoint 2016 environment and Microsoft SQL Server 2016 (SP1). We cant move document libraries or lists to other site collections. Can we shrink SharePoint content database data file? it has crossed more than 50 percent. It might cross 200 GB soon. Please Share your input.

There is no official documentation for SharePoint 2016 and 2019 which describes shrinking of databases. All the documents and forum questions are old and there is no proper guide on this topic. As per the old document we can shrink the database.

https://learn.microsoft.com/en-us/previous-versions/office/sharepoint-server-2010/cc262731(v=office.14)?redirectedfrom=MSDN#DBMaintenanceForSPS2010_ShrinkingDataFiles.

Answer 1

Hi @prabhat tunuguntla ,

in addition on what suggested you may also have a look if you databases are set in SIMPLE or FULL recovery mode.

Full recovery mode is normally set when you have your database in synch via Log-Shipping or Mirroring for High Availability.
You may evaluate to switch the DB in simple recovery mode to gain space into LDF file.

Answer 2

Hi @prabhat tunuguntla ,

Yes, you can shrink SharePoint Content database Data file. The link in your post is also applied to SharePoint Server 2016. Follow the guidelines in the article. DBCC SHRINKDATABASE shrinks the data and log files for a specific database. To shrink individual files, use DBCC SHRINKFILE. To shrink a database by using SQL Server 2016 Management Studio, expand Databases, right-click the database that you want to shrink, choose Tasks, Shrink, Files.

----------

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.