?
Solved

Sales Line 200 Large LDF File

Posted on 2011-10-27
7
Medium Priority
?
729 Views
Last Modified: 2012-05-12
Hi

I have an issue where a server is running Sage Line 200 running with an SQL 2005 server has a 75Gb LDF file whilst the MDF is only 0.9Gb.

Is this something to worry about and what might cause this to occur?

Regards

Dan
0
Comment
Question by:Auratek
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37037844
I suspect this is the common problem of the recovery method set to full and noone doing a backup of the log files.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24779598.html

If you don't want to back the logs up set the mode to simple recovery. If you do, then include a back up of the logs in the maintenance plan.

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37037853
If you plan to set the mode to simple, firstly back the logs up and truncate them.

http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/

Shrinking: http://support.microsoft.com/kb/907511

Lee
0
 
LVL 5

Expert Comment

by:NARANTHIRAN
ID: 37037933
Run the Following command in the isqlw to reduce the LDF file size.

Dbcc shrinkdatabse (database ,0)

U can also add the above command to Sql agent and schedule it, to reduce the LDF files regularly.
0
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.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37038138
You can only shrink after the database and logs have been backed up.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 37038523
Go into SSMS, open a "New Query" on that server, and issue these commands (replace ?dbname? with your actual database name):

USE ?dbname?

EXEC sp_helpfile

--Look in the FIRST column to get the logical name of the LOG file(s);
--replace ?logname? with the LOGICAL log name (not the physical file name)

ALTER DATABASE dbname  
SET RECOVERY SIMPLE

DBCC OPENTRAN
-- hopefully you see no open trans

DBCC SHRINKFILE ( ?logname?, 1 )

ALTER DATABASE dbname MODIFY FILE ( NAME = ?logname?, SIZE = 100MB )


Then, if you want, you can set the recovery back to FULL and take a full database backup.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37038557
If you set it back to full again without modifying the maintenance plan to incorporate a log back up you will end up with a huge log file again. Logs do not truncate automatically in full recovery mode unless they are backed up as part of the maintenance plan.
0
 

Author Closing Comment

by:Auratek
ID: 37248749
These steps helped me shrink the log file. Thanks a lot.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 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