SharePoint Config database Log file too big – reduce it!
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\MSSQL\Data
As we see in the example above, even though the database is only 6 megs, the log file grew to 11 gigs. Let’s reduce that:
1. If you don’t have it yet, download and install SQL Server Management Studio express 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
anc 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.
The result you get should be something like below:
and ofcourse the file size:
ahhh. finally some space.



A friend of mine just emailed me one of your articles from a while back. I read that one a few more. Really enjoy your blog. Thanks
Thanks, Sue! I hope it’s helpful
Hi, I still am running Sharepoint with the SQL Express version. How can I shrink it as it is not in SQL 2005 yet.
Any help would be of great assistance.
Hi, Jay!
The process is the same. These screenshots were even made on a server with SQL Express. All you probably need is to download and install the SQL Server management studio express (the direct link to download can be found in step 1.
In SQL Server 2005 where the Sharepoint_Config Database is located?. Because we cannot find any Database like this.
Our problem is Sharepoint_Config_log file size is 85GB we want to reduce the size of the database.
Hi, Sankar M.
You can do a file search in your server. Just look for the database name (SharePoint_config) and it will find. In my case the files are stored in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData
I'm glad it helped. It should also work for the content log file. Anyway just to be on the safe side, make a backup of the log file before making changes to that one.
Great article. This worked perfectly. One question, does the same hold true for the other databases? My WSS_Content_log file is 2 gigs.
Thanks
Many thanks
I tried this exact process, and my database didn't shrink at all. Mine is almost three gigabytes and I wanted to reduce it to 50megs. It's still at 3 gigs. The script executed with out error and gave me a similar display you have in your example above. What gives?
Went from 12GB to 50MB. Works like a charm. Thanks for the help!
For those having the same problems I had, when you make:
BACKUP LOG [Sharepoint_Config] TO DISK=’D:configLogBackup.bak’
If you receive the error "Cannot open backup device 'D:configLogBackup.bak'. Operating system error 5(Access is denied.)" it is because you must add the user that is currently running the database service with full access to the folder or drive where you are dropping the backup. I had this problem and had to read a little more before getting the solution, so I hope you can get it right here.
Nice guide, thought! Great one!
Excelent quide. Thank you.
HI. I got an error on the messages tab saying: Cannot shrink log file 2 (SharePoint_Config_6af1f30d-5a83-4560-a574-8a332fdfbe21_log) because all logical log files are in use.
How do I go around this? Thanks a lot.
If I’ve used the default WSS 3.0 install with the Express, why can’t I connect to the database with Studio Express? I’ve tried all of my administrator accounts (windows authentication) on that particular server and I always end up with the message “Cannot connect to Serverxx\MICROSOFT##SSEE. “An error has occured while establishing a connection to the server. Wehn connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections…………………. error:26 – Error Locating Server/Instance Specified)……………”
Any suggestions? I need to be able to reduce my log file before I run out of disk space.
Thanks so much for the articles!!
Did you install it to sql server express or Windows internal database (SSEE)? SSEE is a very limited database. Have you tried accessing it with SQL Server Management Studio?
Hi Boris.
Your process worked really fine, and solved my space problem.
What do you suggest for scheduling those tasks, in order to avoid executing them manually?
Thanks and regards.
Thanks, great post! Just what I needed.
Hi,
Hi,
I am facing some issues while doing SharePoint_Config Database transaction log backups:
Database LDF file size has grown to 21.xx GB. To truncate the log file I have created a Maintenance plan to backup log (as per MSDN :http://technet.microsoft.com/en-us/library/cc811604.aspx) , but it failed with following message:
BackupMedium::ReportIoError: write failure on backup device ‘E:\BACKUPS\SQL\TransactionLogs\SharePoint_Config\SharePoint_Config_backup_200910201120.trn’. Operating system error 112(error not found).
I tried following command manually also using T-SQL as mentioned in this article, but same result. Any ideas??
Thanks
Pryank
to give more info: Disk free space is 8.35 GB on backup drive.
Hello
I get a message: Cannot shrink logfile 2 (Sharepoint_Config…) because all logical log files are in use.
What does this mean? Both processes (Shrink -> File) and the SQL command apprear to be successful without error.
Thank you
Thanks a lot !! I can breathe freely now !!!
really superb explanation
thanks you saved me
Very useful. Thank you.
Just encountered this problem, we followed MS suggestion to reduce and worked okay. My question is how do we keep it in check. You said something about “if you don’t perform full farm backup” Does this help keep the file from growing out of control?
Thanks, very good clear article, my log file went from 97Gb to 1 Gb, now thats a space saving!
Hi, I happen to have a configuration file that is 74 GBs. But cannot find the file under SQL 2005 Management Studio. The file is in a weird location (c:\windows\sysim\etc…\data\configuration file… how do I find the database under SQL MAnagement Studio? Thanks,
by the way, this SBS 2003 R2 and the databse refers to a site called intranet. Companyweb works fine.
I tried both solutions and both didn’t work. The second method threw an error message
@Andrew: Which was the error message?
At the schrinkfile command is give an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘’’.
What is wrong?
@Haas:Please check if you’re using normal single quotes. Copy-paste from this place might copy wrong quotes.
Absolutely wonderful, accurate, and needed post. Thank you.
I had ignored my SharePoint_Config file size and it grew to eat up virtually all remaining disk space. The schnifty right-click method didn’t work, but the method involving the 3 queries worked WONDERFULLY.
To re-ask Omar Ruiz de Huidobro’s question, what is the recommended method of scheduling this? A sproc with fired from a scheduled task?
-James
I have the problem of connecting to the database like LT said. My MICROSOFT##SSEE is a windows internal. How do I access this and decrease the size. Thanks for any help
@Jerry: Have you tried installing Sql Server Management studio express? You can find it here:
http://www.microsoft.com/downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en
This will install only management studio without another instance of SQL server.
I just wanted to say how helpful this article was for me. Thank you very VERY much for taking the time to write this.
Yes! Now we can breathe – thanks so much. One missing link for me was logging into the database in SQL Server Management Studio. Use \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
as the server name. It worked for me.
Thanks for the tip, Julie
Thanks for this post! This helped me in shrinking ldf file.
thanks!
HI my SharePoint_Config_log.LDF file size is increased upto 30 gb and i used the same step and it show me same screen also after running command.But it did not shrink my SharePoint_Config_log.LDF file stil it is showing same size but in i got message that:
“Cannot shrink log file 2 (SharePoint_Config_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
“
@RAJ: It looks like your log file was in use while when you were trying to make this. Have you tried again at another time when the DB was not used?
i’m using ms sqlserver 2008. Look like your post doesn’t work for sql server 2008. do you have other solution for that. Thank you.
Hello,
This has helped me out greatly. Freed up about 20GB for me. This is actually the second time I have done it.
At the start you say, ‘If you don’t perform a full farm backup the logs are not emptied’
I run a full backup every night using STSADM.exe
STSADM.exe -o backup -directory e:\backups\sharepoint -backupmethod full
Even though I am doing a full backup the logs still get huge. Am I doing something wrong?