Sales Line 200 Large LDF File

Posted on 2011-10-27
Last Modified: 2012-05-12

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?


Question by:Auratek
    LVL 25

    Expert Comment

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

    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.

    LVL 25

    Expert Comment

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


    LVL 5

    Expert Comment

    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.
    LVL 25

    Expert Comment

    by:Lee Savidge
    You can only shrink after the database and logs have been backed up.
    LVL 68

    Accepted Solution

    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  

    -- 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.
    LVL 25

    Expert Comment

    by:Lee Savidge
    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.

    Author Closing Comment

    These steps helped me shrink the log file. Thanks a lot.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    As an accountant it is essential that I am able to provide accurate and timely information to management and staff.  One of the challenges that I have faced is the need to report on a time period, whether it be a month, quarter, or year, and wanting…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now