Monday, June 11, 2012

Reduce/Truncate Sharepoint Config DB Log file size



                        A very common issue which Sharepoint Server administrators face while maintaining their Sharepoint farm is while maintenance, the free space of the drive where SQL is stored comes down.This is because of the Sharepoint log file size increasing.This leads to performance issues on those servers.

It would be ideal to shrink/truncate the size of these log files in a timely manner.The below steps helps in resolving this issue.
1. Back up the Sharepoint log file
Open SQL 2008.Select New Query.
Type the following:
BACKUP LOG [Sharepoint_Config] TO DISK=’F:\configLogBackup.bak’
GO
And click Execute

2. Change Recovery model to SIMPLE

Change the DB recovery model to Simple (Right click on the SharePoint_Config and click on properties -> Options  -> Recovery model: change it to SIMPLE.




                                                     (OR)
Query :
ALTER DATABASE Databasename
SET RECOVERY Simple

3. Shrink the Sharepoint Config log DB to 50 MB
Clear the query or open another query tab and enter the next command:
DBCC SHRINKFILE('Sharepoint_Config_Log',50)



4. Revert back Recovery model to FULL
Change the DB recovery model to FULL (Right click on the SharePoint_Config and click on properties -> Options    -> Recovery model: change it to FULL.

5 comments:

  1. Thanks, this helped a lot when our config log grew to 96 GB!
    One detail to add: Step 3 needs a USE statement prior to the shrink.

    ReplyDelete
  2. Thanks, My sharepoint config Log reached 234gb . Attempted several other methods of shrinking the file, this is the only way I found it to work. Thanks Again.

    ReplyDelete
  3. Thanks man, Worked like a charm!

    ReplyDelete
  4. i am getting the following error:

    Msg 4214, Level 16, State 1, Line 1
    BACKUP LOG cannot be performed because there is no current database backup.
    Msg 3013, Level 16, State 1, Line 1
    BACKUP LOG is terminating abnormally.

    ReplyDelete