What are the risks of changing SharePoint_Config db recovery model to Simple?

Question

My SharePoint_Config database log file grows to the extent that it reserves very much disk space for seemingly no good reason. In my development environment I changed its recovery model to simple to enable release of disk space, and avoid the risk of the log file to grow at the same pace again.

The next step was to shrink the log file with the attribute Empty file by migrating the data to other files in the same file group.

When that was done, a third of the disk (33 GB) was released, and my development environment seems OK. My guess is that this is perfectly OK in a development environment, where a recovery and database logging is not that critical.

However, could this be done on a production environment as well? I understand that all my logs are gone, but what are the risks of proceeding the way I do in production? What are the risks of changing SharePoint_Config db recovery model to Simple?

Answer 1

There are two ways to look at this concern.

First is to literally take what Microsoft says in face value, because they are most of the time correct about the operational best practices for their own products.

Said so, if you follow the guideline mentioned here
http://technet.microsoft.com/en-us/library/cc678868(v=office.14).aspx

It says

Additional notes Transaction log files. We recommend that you back up the transaction log for the configuration database regularly to force truncation, or — if you are not mirroring your system — change the database to run in Simple recovery mode. For more information, see Transaction Log Truncation (http://go.microsoft.com/fwlink/p/?LinkId=186687).

So simply recovery mode is the way to go for if mirroring is disabled.

Second way is to look at how your backup and recovery strategies are defined. What is your restoration approach ?

Powershell driven complete farm backup and Central Admin driven farm restore or SQL driven restore through database attach over the same server aliases and hardware. Simple recovery would be the preferable mode for the first case and for the second case its preferrable to keep the SharePoint Config db in a full recovery mode just to ensure a "as is" replica of the previous state from the point the farm failed.

MS outlines this as mentioned below

The configuration database is backed up when you perform a SharePoint farm configuration and content backup, and some configuration settings from the database are exported and stored as XML files. When a farm is restored, the configuration database is not restored. Instead, the saved configuration settings are imported. The configuration database can be successfully backed up and restored by using SQL Server or other tools if the SharePoint farm is first taken offline.

Literally, the ifs and buts of your concern are highly subjective based on your choice of backup and recovery plans since sharepoint supports quite a few of them. Therefore, you can choose to go for a simple recovery model for the configuration database and shrink down the logs in production only if it satisfies the conditions mentioned above so that the precautions are taken accordingly.

Answer 2

By default, the SharePoint_Config database is set to Full recovery model. But, Microsoft recommendation is to set the recovery model for your SharePoint_Config database to simple in production (reference: http://technet.microsoft.com/en-us/library/cc678868.aspx). You can change the recovery model for SharePoint_Config after you create the SharePoint_Config DB.

Never shrink your databases be it development, production, or wherever. Because when you shrink your DBs it increases the fragmentation which reduces performance. It is not worth the storage amount you free up (reference: http://www.microsoftvirtualacademy.com/training-courses/tuning-sql-server-2012-for-sharepoint-2013-jump-start#fbid=oNaeMo8B3B7 (in one of those videos they mentioned this, I don't remember which one) and http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/).