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 FilesMicrosoft SQL ServerMSSQL.1MSSQLData
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 🙂
[…] SharePoint Config database Log file too big – reduce it! […]
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.
Thankyou!
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 ServerxxMICROSOFT##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!!
Hi, I had this problem. Paste this into the server name field:
\.pipeMSSQL$MICROSOFT##SSEEsqlquery
It should then connect.
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:BACKUPSSQLTransactionLogsSharePoint_ConfigSharePoint_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:windowssysimetc…dataconfiguration 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
: 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?
: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
: 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 \.pipeMSSQL$MICROSOFT##SSEEsqlquery
as the server name. It worked for me.
Glad you posted this, helped me greatly
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.
“
: 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:backupssharepoint -backupmethod full
Even though I am doing a full backup the logs still get huge. Am I doing something wrong?
This is all well and good if you can locate the sharepoint database in sql express management console, none of the recommendations to find it here have helped. any advice would be appreciated. I’m using SBS 2008. Thanks.
: Make sure, you’re opening the right database. If I’m not wrong SBS creates its own database for SharePoint.
Excellent guide ! Thanks Angel for the permission issue.
I had to use this connection string to get to Windows Internal Database
\.pipeMSSQL$MICROSOFT##SSEEsqlquery
Kudo Gomiunik
When I try and do the same for the WSS_content file I get:
Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘WSS_Content_log’ for database ‘SharePoint_Config’ in sys.database_files. The file either does not exist, or was dropped.
Any ideas?
First Class guide, thanks for posting this, it really helped me out.
[…] https://sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ […]
Hi! Thank you to author for the info!
Unfortunatelly I’ve met the same to LT problem. We use WSS3.0 on VM with limited disk space. According to guide above I installed SQL Server Management Studio and tried to connect to database and got a message “Cannot connect to ServerxxMICROSOFT##SSEE. “An error has occured while establishing a connection to the server. When 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)……………”
As far as I understand we have WID with our WSS installed.
Are there any idea how we can reach this database?
Thenks in advance,
Alex
I’m not easily impressed. . . but that’s impsrsenig me! 🙂
Should this make the logs folder disappear from the 12 hive?
No, this does not affect the 12 hive. It affects the Content database logs.
Hello,
I Am running out of memory, therefore I thought to shrink the logfile.
Everything went well untill I actualy schrank the database. the database decreased from 12 gig up to 13,5 gig. Now I’m realy running out of memory.
Who can help me further.
greatings bas
[…] So for one of my clients I manage a Sharepoint service. Recently their server ran out of disk space and it was primarily caused the by the config log of sharepoint exploding to 17GB (!). Additionally, the text log files associated with it were of a similar size. Absolute madness. The disk went from 40GB free to full in a 2 hours. No particular errors were associated with this, apparently its the magic of sharepoint. I found a rather useful blog here: https://sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ […]
THANK YOU, THANK YOU, THANK YOU!!!! Just what I needed!
It worked like a charm!
One question: do we need the log backup for anything or can that thing be deleted? I do run the stsadm -o backup -directory C:SPbackup -backupmethod full command daily to back my SP site. Should that be enough to restore the SP server or do I need the log as well?
2 years and 7 months later and this post is still giving back.
thanks
worked perfectly.
Thank you very much for this article !
I was experiencing log size problems with Sharepoint Services 3.0 SP2 and integrated SQL instance.
NB : Here is the name of the instance to use with SQL Management Studio Express
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Thanks…that’s what I needed on my SBS 2003 box.
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Excellent thanks couldn’t connect JoeF that worked. db now simple at 50mb. down from 7.8
finally someone posting with the right chain of \\
Thanks for the post. Thanks for comments. Very helpfull.
I get the following message, when I try to backup the database;
Msg 262, Level 14, State 1, Line 1
BACKUP LOG permission denied in database ‘SharePoint_Config_a621cd13-15e7-4c4f-8f8a-fd1fadc25c26’.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Perhaps you can check SQL permissions on the SharePoint_Config_a621cd13-15e7-4c4f-8f8a-fd1fadc25c26 database.
[…] Here is a good blog http://blog.hompus.nl/2010/06/22/keeping-your-sharepoint-2010-development-databases-small/ and https://sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ […]
Had the same issue, problem is I only have “KB” on my C: drive. yes, not “MB” or “GB” but “KB”. Thanks for posting this up.. you are helping a lot of people.
You are a gift from above Boris. Works perfectly. Thanks as well Jose Angel, encountered exactly the same and was able to get away with it with your post.
Thanks! This solved my problem like the others. I at first couldn’t find the database so the link to the server also was key!
As a side note, you don’t have to back up the database to be able to shrink it. Just change the Recovery Model to Simple from Full and you can shrink with no problem. To access this option, right-click on the Database, select Properties and then click on the Options page.
Hello!
Here is the almost
same way to shrink sharepoint database transaction log.
Thanks!
I have been sweating bullets over this. Even pulling the trigger on this, put me into a serious panic attack, but it worked.
Thank you for posting these kind of solutions out here. I can’t even tell you how much I appreciate it and how much you just saved my client!
I used severname/microsoft##ssee to connect to my db on my server 2003. My log file is 135gb but I am running query 1 now
I followed the steps and they worked great but what exactly did I just do?
These instructions are great, and I did need the updated connection string (\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query). While I was in there I changed the database option for Recovery Model from Full to Simple. This should prevent the database log from growing too large. We don’t use Sharepoint heavily enough to warrant Full logging anyway.
Does anyone know how I can register this database with my full SQL Management Studio (what string to use to connect to the Sharepoint databse from another sever running SQL Management Studio)? I’d like to set up a SQL-managed backup to disk scheduled job.
Craig
did you get any answer on this? Someone removed sql 2005 express studio and i need to use sql 2008 management studio to do this and other things.
From 16GB to 50MB. Amazing! Thank you very VERY much for the help!
Go an error with the second script in the
DBCC SHRINKFILE (N’SharePoint_Config_log’ , 50)
GO
What would be the name used here is this not the same name as above. SharePoint_Config?
Is the SharePoint_Config_log do I need like a full path to a actual file name?
SharePoint_Config_log.ldf
I get a could not locate file for database ‘master’
SharePoint Config database log file too big. Solve it
USE SharePoint_Config
GO
ALTER DATABASE SharePoint_Config SET RECOVERY SIMPLE
DBCC SHRINKFILE(N’SharePoint_Config_log’, 1)
ALTER DATABASE SharePoint_Config SET RECOVERY FULL
GO
Hey thanks for this, problem solved! Great guide.
Kudos! You saved my bacon, thanks! After figuring out the right syntax it worked like a charm.
It worked ! Thanks !
helpme on this
SharePoint_Config_log.LDF this is the database name and size is 107 gb it is located in MySQL\data folder but dosent show up in management studio there is one more database WSS_Content_log.LDF and the size is 149 gb I have to reduce the size of these immediately and these dosent show up in sql management studio
Thanks alot, have been battling with space issues for about a month. Server kept eating up space like fire in the woods. great article !!
You need be a bit careful while doing this….specially shrinking without taking Transactional log backups of Config dbs. Read my blogpost for more:
http://shareyourpoint.wordpress.com/2013/03/22/how-to-manage-large-sharepoint-configuration-database-logfile/
[…] https://sharepointboris.net/2008/10/sharepoint-config-database-log-file-too-big-reduce-it/ […]
I got it thanks but i am having sharepoint_config2 log file which having 12 gb but in database it shows only sharepoint_config only……how to i find this above mentioned database help me….
thanks advance.
just like that i went from 1gb free to 32gb free on my server c: drive! Just smashing! thank you thank you thank you!!!
just noticed my c: drive on my sharepoint 2007 server is very low on space, after some investigating, my sharepoint_config…._log.LDF file is 37Gb and my wss_content_log.LDF file is 23Gb
the sharepoint server does not have any version of sql managment studio express installed
help! what do I do
You can download sql management studio Express for free (https://www.microsoft.com/en-us/download/details.aspx?id=8961)