Sales Line 200 Large LDF File

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
AuratekAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
Lee SavidgeCommented:
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
 
Lee SavidgeCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Naranthiran DSystem AdministratorCommented:
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
 
Lee SavidgeCommented:
You can only shrink after the database and logs have been backed up.
0
 
Lee SavidgeCommented:
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
 
AuratekAuthor Commented:
These steps helped me shrink the log file. Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.