27 June 2010

Shrinking the Sharepoint Config DB Log file

One of the SBS servers I look after showed a disk space issue. I had already disabled the WSUS log file, so was a bit puzzled where all the disk space had gone. Upon closer inspection using my favority Treesize tool, I found that the Sharepoint Configuration Database transaction log had grown to 11GB. Well the client uses Sharepoint a bit, but surely this was out of the oridinary on a 160MB DB data file.

I fired up SQL 2005 Express Managment Studio that is installed with SBS (make sure you run as Admin), but was initially unable to connect to the Windows internal database, as it is hidden from general view. However after a bit of googling I found that I had to connect using

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

to access the database. Next I wrote a quick script that shrinks the log file:

USE Master
GO
BACKUP LOG [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6] WITH TRUNCATE_ONLY
GO
USE [SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6]
GO
DBCC SHRINKFILE([SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6_log],50)
GO


The Database name may vary from system to system (but appears the same for all SBS 2008 installations), so the above will need to be adjusted to the name of your sharepoint config database name.

While you are there, you may want to check and apply the same fix to the actual Sharepoint Web content database:


USE Master
GO
BACKUP LOG [ShareWebDb] WITH TRUNCATE_ONLY
GO
USE [ShareWebDb]
GO
DBCC SHRINKFILE([ShareWebDb_log],50)
GO


Happy Shrinking!

10 comments:

  1. Thans! I don't normally post on these kinds of things, but it was nice and concise here and saved me much time... for the record, my DB file was the same name, so perhaps that's a standard? In any case, mine was also pegged at 11GB, and now it is at a nice even 50MB as your script makes. Thanks again!

    ReplyDelete
  2. Fantastic job - And the funny part was, that I didn't even have to change my DB name - it was the same as yours!!

    I had been googling around for a long time, to solve this. And this nice little script did it.

    Thanks a bunch.

    Kim

    ReplyDelete
  3. I'm going to link to this post, great info. I have a script that does sort of the same thing in a different way, though yours is much better and far easier to implement and maintain. I was at 7Gb, now down to under 1.

    My continuing problem is that the log file grows and grows, and it will be back up to 7+ Gb in two days. Maybe i have usage tracking or something enabled. I removed all of my column indexing, well see how it goes.

    Thanks again :)

    ReplyDelete
  4. Hi Chris

    Thanks for the feedback, I am glad the script helped you out.

    Your site must be very busy? What is the size of the actual DB file? It indicates something fishy going on... Have a look at the reports you can get from the DB; just right click on the Share Point config database, then select Reports > Standard Reports and pick any one of them. It may help to narrow down what is happening.

    Good luck!

    ReplyDelete
  5. Thank you for this post!! I've been trying to understand what's going on. My log was 20GB and growing by the hour!! I'm not familiar with this db or what makes it grow, but I'll keep an eye via the reports!

    ReplyDelete
  6. This saved a customer of mine around 4.5GB of disk space on their system drive, thank you!

    ReplyDelete
  7. Thank You, the scripts opened 16 Gigs of space on the server I was working on. A real life saver!!

    ReplyDelete
  8. this site is actually useful compared to the others out there referencing the same problem

    thanks for sharing!

    MrM

    ReplyDelete
  9. I worked six hours a day persisted. simply believed would be resolved. it was thanks to you. thank you very much

    ReplyDelete
  10. Thanks for this, i was unable to detact the SUSDB, ran your query and it fixed our issue.

    ReplyDelete