SharePoint and .NET: How to shrink SQL / SharePoint / database log files

SharePoint Config database logs are one thing to keep an eye on since they do have a tendency to grow. If you don’t perform a full farm backup usually the log doesn't get emptied and it just keeps bloating.

If you’re running SQL Server Express with default installation, you can find the files in

C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQLData

else if you can find with named instance like

C:\Program Files\Microsoft SQL Server\MSSQL10.SP2010TST\MSSQL\DATA

As we see in the screen above, even though the database is only 6 MB, but the log file grew to 11 GB. Let’s reduce that:

1. If you don’t have SQL Server Management Studio yet, download and install it from here.

2. Run the Management Studio and connect to your SQL Server.

3. Expand “Databases” and select your config database “SharePoint_Config”.

4. Right Click it, select Tasks –> Shrink –> Files

5. In the new window select Release unused space and click OK.

If that doesn't decrease the database size much, do the following:

1. First to be on the safe side, let’s back it up (this step is optional)

Select New Query

type the following:

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

Where SharePoint_Config is the name of your config database file and D:\configlogbackup.bak is the location and file name of where you’ll make the backup.

And click Execute

This may take a while if your log file is big.

2. Next clear the query (or click New Query again) and enter the following commands

BACKUP LOG [Sharepoint_Config] WITH TRUNCATE_ONLY
USE [SharePoint_Config]
GO

and click Execute again

3. Clear the query or open another query tab and enter the next command:

DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50)
GO

The 50 in the command above sets the size in MB to truncate the log to. If your config db is of different name, replace the SharePoint_Config part above with your config db name.

And click Execute yet again.