Ever faced the uphill task to manage large (over 50 GB at times) of
SharePoint configuration database log file (can be MOSS 2007, SharePoint
2010 or SharePoint 2013). I have and to the extent that the Logfile
might end up eating all the drive space.
Different people have different opinion on how to approach this and I have my own. Here goes my 2 cent:
1. Connect to the Configuration database SQL Server.
2. Run the following command against the configuration database (SP_config_XXXX):
DBCC SQLPERF(logspace)
The above command will show you the amount of free space available. It generally will be 99% used. Issue a checkpoint now. Run:
DBCC SQLPERF(logspace)
Checkpoint
3. Take your Transactional logfile backup using SSMS:
Start > All Programs > Microsoft SQL Server 2008 > SQL
Server Management Studio > Connect to the Configuration database SQL
Server > Right Click the Config database name > Tasks > Backup
> Under Backup Type Select “Transaction Log” > Provide a Backup to
location > Under Options make sure “Truncate the transaction log”
radio button is selected > Set Backup compression select “Compress
backup”
Wait for it to finish may take 5 -7 minutes but depends on server resources available.
4. Once Step 3 is complete Run :
DBCC SQLPERF(logspace)
You should see that the logspace used % for the config db is now 0.7% or so.
5. Issue the command to find the Config database logfile name:
Select * from Sys.Database_Files
Copy the name of the Configuration database logfile cause you are going to need it in the next step.
6. Shrink the logfile now using the command below. My Logifile name
is SP_Config_prod01_log and I used 1000 MB since I did not want my
logfiles to be 512 KB in size and then grow rapidly (I hate illusion)
DBCC ShrinkFile (‘SP_config_prod01_log’,1000)
7. Now the fun starts. You will be surprised to note ladies and
gentlemen that the logfile has not shrunk. Indeed we live in a world of
illusion. At this point I will request you to maintain peace and not run
like a headless chicken and follow these steps.
Redo Step 3 to take the Transactional logfile backup again and this
time under Options in the backup menu > under Overwrite media >
Select “Append to the exisiting backup set”.
This time the backup will be much faster than Step 3.
8. Issue the Shrink command again:
DBCC ShrinkFile (‘SP_config_prod01_log’,1000)
9. Check the Log Size using:
DBCC SQLPERF(logspace)
The Config DB logfile size should have gone down to 1000 MB.
10. And the most important. Thank God and not me that you reached this far without screwing up your production server.