SQL Express 2005 log is larger than the Database ?

Hi People,

I'm running WSS 3.0 (SharePoint 2007 free equivalent) on my Windows Server 2003 Standard editon 32 bit.

Can anyone please shed some light in regards to the size of SQL Server log files and the database ?

Browsing to C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data I can see that there are the following files:

WSS_Search_PRODWSS01.mdf --> 560 MB Database file
WSS_Search_PRODWSS01-VM_log.LDF --> 3.2 GB Log file

How is that happening ?
my only understanding is that the database file should be larger than the log files.

Is there any way to shrink the log file in the SQL Server Express 2005 edition ?

Thanks in advance.
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
Rainer JeschorConnect With a Mentor Commented:
this normally indicates that you have never run any log backup.
For each an every transaction (INSERTS and UPDATES), SQL server creates an entry in the log file. As this database is the search, the search crawls are running frequently, inserting and updating data.

Question is: please review the recovery model of this database. It should be set to "Full".

Steps to solve:
- Open SQL Server Management studio (SSMS) and run a full backup first (if you have not done before)
- Then run a transaction log backup
- Then you might try to shrink the log file using SSMS. For me I sometimes have to run this a couple of times until the file got resized

As you use SQL Server Express edition there is no SQL Agent where you can configure maintenance plans to run the backups regularly.
There are some other possibilities published in this MS Support entry:

Senior IT System EngineerIT ProfessionalAuthor Commented:
Mr. Rainer,

Thanks for the clarification, so in this case do I need to do the following command in the SSMS against the WSS_Search_PRODWSS01 database:


Regarding the backup, how can I initiate the backup from ?
Does the Transaction log backup can be initiated from within the SQL Express edition ?
Rainer JeschorConnect With a Mentor Commented:
as far as I know, MS does not prohibit changing the database recovery model for SharePoint databases, so it is more a matter of: Do you want to have a point-in-time recovery or not?

Which SSMS edition/version do you use?
I have currently just the included one from the full SQL Server edition (SQL 2008R2 and SQL 2012). Normally you can do everything from within SSMS by right click on the database,
 "Tasks" -> "Backup" and then choose Full first (and the second time transaction log). When you run transaction log backup, you can set a property in the options tab to truncate the log.

Recovery Model:
Right click on the database -> Properties -> Options -> Recovery Model -> Simple

First you have to run the Full and the transaction log backup. Then you can shrink and finally you can set the recovery model.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Senior IT System EngineerIT ProfessionalAuthor Commented:
Do you want to have a point-in-time recovery or not? not really needed for my case, since this database only used during the business hours for submitting the form and DOcument repository only.

I'm using SSMS 2005 to conenct to the SQL Express Edition.

1. Run Full Backup from SSMS and select the location into another drive
2. Run second backup Transaction Log backup only to commit the logs into the DB.
3. Set the Recovery mode to Simple
4. Shrink the log file into... (how much is suggested ? does 1 MB is too small ?
5. Set the recovery mode to Full

Cool, so can I do the steps above during the business hours ? is there any risk of data loss ?
Rainer JeschorConnect With a Mentor Commented:
I would shrink the file to about 20mb.
And I would not set the db to full mode back, instead i would create a backup job to make a full backup every day before business hours.
And normally no risk, but you know Mr Murphy?
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.