Shrink SharePoint_Config database
Nowadays if we talk about SharePoint, 7 out of 10 discussion is about SharePoint online. Seems not many people understand SharePoint on-premise. Today one of our development environment (a SharePoint 2019 on-premise farm) encountered an error. I would like to log it on blog before everyone forgot how to troubleshoot.
Today, when I tried to create a new collection it return “Object reference not set to an instance of an object” error with a Correlation ID. Have you been panicked by above error before? It is a very common ASP.NET error. When I saw this error first time I am almost shit my pants. “Did I break the SharePoint code?” is a straightforward consideration. However after years of SharePoint administration experience I can tell you it is just another generic error. Microsoft software tend to avoid give you accurate error message.
To know what was happened, goto “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\LOGS” (default path of SP2019 log) , look for the log file there. If the error is triggered at client side browser, most likely it is logged in your WFE server. If the error action is on CA or backup, check the APP server. If you’re not sure, just check all the servers in your farm.
Open the log file according to error time. Search with the error correlation ID. There will be multiple log related with the same correlation ID. You may want to read all of them. But I will look for error message with level “Unexpected”, “Critical” or “High”. This time I found an error :
Unexpected System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SharePoint.Utilities.SPSqlCommand.get_ReturnValue() at Microsoft.SharePoint.Administration.SPConfigurationDatabaseSiteLookupProvider.AddSite(SPSiteLookupInfo info, Boolean resyncMode)
The error did not tell me what happened but I know something wrong on SQL Server. By checking the SQL server I know the problem is data disk full. The data drive is fully occupied. No more data can be written into database. Therefore create site collection will not work.
My farm is installed on MS SQL Server 2019. By default the data are saved into {data disk}:\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA. If you are not sure, open the MS SQL Management studio, connect to your SQL server, right click on any database -> Properties. In the prompt up windows look for “File”.
In some environment, different database files can be saved into different disk and path.
In my situation, all database files are stored in same directory. I can immediately find out the biggest file SharePoint_Config_log.ldf have occupied over 80% of my data disk.
There are 2 tasks you should do:
1. Consider change SharePoint_Config to simple recovery model
2. Shrink the database (backup or take a VM snapshot if it is production environment)
Regarding task 1, take a look to official reference Database types and descriptions in SharePoint Server. Under Configuration database section, we can see the “Default recovery model” is Full. Microsoft recommend that you keep the configuration database on the full recovery model and take backups to truncate the log files. However, I don’t believe it is necessary for non-production environment. Full recovery model allow you to restore database to any point of time but the database transaction log will grow to huge size because there are a lot of I/O this database even no user have login. Also, most of time you can simply reverse the change you made on configuration. Why bother to perform restore? In addition, back to SP2010, it is recommended to use Simple model: Database types and descriptions (SharePoint Server 2010) by Microsoft.
For task 2, here is a SQL statement I used to shrink my database in quick and dirty way:
BACKUP LOG SharePoint_Config TO DISK=’NUL:’ WITH NO_CHECKSUM, CONTINUE_AFTER_ERROR
Use SharePoint_Config
dbcc loginfo
DBCC SHRINKFILE (SharePoint_Config_log, EMPTYFILE);
If your database size is huge, the command will take quite long to complete. (It took me 15min for a 100GB database) Also, you need to run again above command lines several times until the occupied space is released.
Experienced SQL administrators will warn you not to do it because it will harm the SQL performance. The idle way to shrink the database is to perform manual full backup. After backup, the log data will be gone automatically. In my experience, the “auto shrink” will happen over the weekend and sometimes it just never happen! For non-production environment, why bother to wait? I need to fix the space problem and continue my work!
After release the space (my SharePoint_Config_log.ldf reduced from over 50GB to less than 10MB) the SharePoint 2019 farm is back to normal. I can create site collection now.
