?
Solved

SQL Express 2005 log is larger than the Database ?

Posted on 2013-12-18
6
Medium Priority
?
697 Views
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.
0
Comment
  • 3
  • 3
6 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 39728456
Hi,
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:
http://support.microsoft.com/kb/2019698

HTH
Rainer
0
 
LVL 8

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:


ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(WSS_Search_PRODWSS01-VM_log.LDF)
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Regarding the backup, how can I initiate the backup from ?
Does the Transaction log backup can be initiated from within the SQL Express edition ?
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 2000 total points
ID: 39728478
Hi,
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,
Backup:
 "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.

HTH
Rainer
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

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 ?
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 2000 total points
ID: 39728523
Hi,
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?
KR
Rainer
0
 
LVL 8

Author Closing Comment

by:Senior IT System Engineer
ID: 39730598
Thanks !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 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