SQL Express 2005 log is larger than the Database ?

Posted on 2013-12-18
Last Modified: 2013-12-19
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.
  • 3
  • 3
LVL 44

Accepted Solution

Rainer Jeschor earned 500 total points
ID: 39728456
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:


Author Comment

by:Senior IT System Engineer
ID: 39728463
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 ?
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 500 total points
ID: 39728478
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.

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.


Author Comment

by:Senior IT System Engineer
ID: 39728505
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 ?
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 500 total points
ID: 39728523
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?

Author Closing Comment

by:Senior IT System Engineer
ID: 39730598
Thanks !

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now