Solved

SQL Express 2005 log is larger than the Database ?

Posted on 2013-12-18
6
563 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 500 total points
Comment Utility
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 7

Author Comment

by:Senior IT System Engineer
Comment Utility
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 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Author Comment

by:Senior IT System Engineer
Comment Utility
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 500 total points
Comment Utility
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 7

Author Closing Comment

by:Senior IT System Engineer
Comment Utility
Thanks !
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

771 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

11 Experts available now in Live!

Get 1:1 Help Now