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
If the database name contains a ' - ', you will get an error message (below).
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;
GOBrowse 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.
Been trying for years to get this issue sorted. Followed many pages of advice and hundreds of hours. Nothing every proved successful.
ReplyDeleteYours took 5 mins and recovered 35GB from my 100GB drive. Delighted.com
Thanks for the help, it works.
ReplyDeleteWon't setting the recovery mode back to full only postpone the issue for later down the road again?
ReplyDeleteFantastic, saved me 20Gb.Well done
ReplyDeleteThanks Dave, it worked! It released almost 20gb! :-)
ReplyDeleteThank you sweet jesus...err Nick!!
ReplyDeleteIf you are getting an error - Cannot connect to Database - run SQL Management Studio as administrator.
ReplyDeleteI had an old server that was running for the better part of 5 years. You just saved me 260GB worth of space. Thank you.
ReplyDeleteThanks!
ReplyDeleteGreat post, helped a ton.
ReplyDeleteThanks a lot - it worked like a charm - also I noticed, MSSQL proccess is not taking so much of CPU as it did before!
ReplyDeleteThank for this trick ! 45 Go recovered !
ReplyDeleteYou "ROCK" I have been trying to figure out for hours what to do. Your instructions are great! Thank you.
ReplyDeleteI 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 >>>>>
ReplyDeleteThanks mate. YOu just saved me a heap of time!
ReplyDeleteThanks same as many, founds tones of bad tricks, this one works !
ReplyDeleteThis tutorial have saved 50GB of space in one of my servers!!!
ReplyDeleteThanks a lot Nick Chard!
anyone knows if this procedure can be used to every kind of log from databases from this SQL Server version?
ReplyDeleteThank you brother!
ReplyDeleteThank you in 2021!