Sharepoint Config SQL logs growing out of control

Today I woke up, bags packed, ready to go to Taiwan for Computex, but I also found a notification from SCOM telling me the SQL box had nearly filled up its harddrive.

Upon investigating I found the Sharepoint Configuration Log has grown to over 6GB in only a few short months, HUH? Why was this happening? I only have a couple of Sharepoint sites and they arn't even heavily used and I do daily SQL backups via Microsoft DPM 2010, shouldn't the logs be truncated at this point?

Actually no, DPM 2010 won't truncated the logs because the Sharepoint databases are set to full recovery mode by default, which is critical for log shipping and mirroring funtionality that you may need with a large Sharepoint farm deployment. I only have a very small Sharepoint deployment but I still don't want to disable the full recovery functionality at this stage.

Instead I have scripted a weekly log truncation via the following SQL Query script.


SHAREPOINT CONFIGURATION LOG TRUNCATION

USE Sharepoint_Config

GO

            DBCC SHRINKFILE(Sharepoint_Config_log, 1)

    BACKUP LOG Sharepoint_Config WITH TRUNCATE_ONLY

            DBCC SHRINKFILE(Sharepoint_Config_log, 1)

This took a LONG time for my 6GB log, but the result was beautiful, huge reduction on my logs and SCOM notification dismissing themselves. Of course you may want or be required to keep your logs in your organiziation. If you have this requirement you can simply export the log before you truncate it.


SHAREPOINT CONFIGURATION LOG BACKUP

BACKUP LOG [Sharepoint_Config] TO DISK=’D:configLogBackup.bak’



Hopefully one is all resolved now!