Solved

SQL Express 2005 log is larger than the Database ?

Posted on 2013-12-18
6
640 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 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 7

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 500 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 7

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 500 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 7

Author Closing Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

696 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