Thursday 24 November 2011

SBS 2008 C drive runs out of space - shrink SharePoint database log file

I have seen serveral cases recently where a server running Small Business Server 2008 has run out of space on the C Drive. Using Tree Size, I identified a folder which contained a large amount of data:

C:\Windows\SYSMSI

The area of this folder which contained all the data was

C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

This folder contained many SQL databases and log files, and after some research I discovered that my largest files were Transactional Log files for Sharepoint.

The biggest of these files was


SharePoint_Config_ffffffff-ffff-ffff-ffff-fffffffffffffffffff_log.LDF

 and was up to 16Gb in size, but other culprits were


ShareWebDb_log.LDF
WSS_Content_fffff..._log.LDF



Large SharePoint log files
 




All these files are log files which can easily be shrunk down to a manageable size. SQL Server Management Studio is required for this.


To run SQL Management Studio, select

Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express



 


















Server name is
\\.\pipe\mssql$microsoft##ssee\sql\query
 

Then select Connect

Expand Databases and locate the datebase with the large log file



















Right click on the database and select New query

























The query window will appear on the right. Enter the following text:

ALTER DATABASE Database_name SET RECOVERY SIMPLE;
GO








Then click the Execute! button


If the database name contains a ' - ', you will get an error message (below).



In this case, the database name must be surrounded by " ", eg:


ALTER DATABASE "SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6" SET RECOVERY SIMPLE;
GO


Then select Execute!



You should see a message which reads Commad(s) completed successfully

Next, Select the database


Right click > Tasks > Shrink > Files


Change File type to Log

Click OK



Once this process has completed, right click the database again and select New query
Enter the following command (use " " around database_name if it contains ' - ')

ALTER DATABASE Database_name SET RECOVERY FULL;
GO

Execute!


Browse to the folder location

C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

and you should see a big difference in the size of the log file.

19 comments:

  1. Been trying for years to get this issue sorted. Followed many pages of advice and hundreds of hours. Nothing every proved successful.
    Yours took 5 mins and recovered 35GB from my 100GB drive. Delighted.com

    ReplyDelete
  2. Thanks for the help, it works.

    ReplyDelete
  3. Won't setting the recovery mode back to full only postpone the issue for later down the road again?

    ReplyDelete
  4. Fantastic, saved me 20Gb.Well done

    ReplyDelete
  5. Thanks Dave, it worked! It released almost 20gb! :-)

    ReplyDelete
  6. Thank you sweet jesus...err Nick!!

    ReplyDelete
  7. If you are getting an error - Cannot connect to Database - run SQL Management Studio as administrator.

    ReplyDelete
  8. I had an old server that was running for the better part of 5 years. You just saved me 260GB worth of space. Thank you.

    ReplyDelete
  9. Thanks a lot - it worked like a charm - also I noticed, MSSQL proccess is not taking so much of CPU as it did before!

    ReplyDelete
  10. Thank for this trick ! 45 Go recovered !

    ReplyDelete
  11. You "ROCK" I have been trying to figure out for hours what to do. Your instructions are great! Thank you.

    ReplyDelete
  12. I love you MAN..... No I don't want your Budweiser.... Thank you so much. your instructions are perfect and after all the reading I have done without a doubt YOU ARE ONE SMART PERSON >>>>>

    ReplyDelete
  13. Thanks mate. YOu just saved me a heap of time!

    ReplyDelete
  14. Thanks same as many, founds tones of bad tricks, this one works !

    ReplyDelete
  15. This tutorial have saved 50GB of space in one of my servers!!!

    Thanks a lot Nick Chard!

    ReplyDelete
  16. anyone knows if this procedure can be used to every kind of log from databases from this SQL Server version?

    ReplyDelete