SQL Server Large Log Files

Posted on 2011-10-31
Last Modified: 2012-05-12
Hi Guys,

Basically I have some very large log files for my SQL Server Databases. We don’t have a DBA and I am in need of assistance.

I have two SQL Servers running Windows 2008 R2 64bit with SQL Server 2008 running Databases with mirroring enabled and have a witness server too.

I have 9 databases setup on the server some more heavily used than others. Our Main database is as follows...

MDF File = 478Mb (Autogrow by 10% Unrestricted)
LDF File = 233GB (Autogrow by 10% Unrestricted)

Last Database Backup 03:34
Last Log File Backup12:30
Recovery Model Full
Mirroring Status "Fully Synchronized"

In SQL Server Agent Manager there is a job scheduled that runs a maintenance plan at 19:00 daily which

    Takes Full Backup of the DB to the local disk for all user databases.
    Deletes all backup files older than 2 days with .bak or .trn file extension.

There is another job set to take Transaction log backups hourly every day.

In the backups folder there are transaction log backups that are between 200Mb and 700Mb every hour.

Please can you advise what I can do as I don’t believe the log file should be this disproportionate to the Database file?

Running “DBCC OPENTRAN” I get

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (219127:93:1)

Running “DBCC SQLPERF(Logspace)” I Get

Database Name      Log Size (MB)      Log Space Used (%)      Status
Main_Multibrand      239051.6      96.17966            0

Running “select name, log_reuse_wait_desc from sys.databases” I get

name                              log_reuse_wait_desc
GreenThings                        NOTHING
Import                              LOG_BACKUP
Main_Multibrand                  REPLICATION
Furniture                        LOG_BACKUP
Assoc                              LOG_BACKUP
Future                              LOG_BACKUP
Food                              LOG_BACKUP
Main_Multibrand_S                  LOG_BACKUP
discounts                        LOG_BACKUP
Survey                              LOG_BACKUP
Home                              LOG_BACKUP
Care                              LOG_BACKUP

If you need any further information then please shout.


Question by:Nigel_Taylor
    LVL 17

    Expert Comment

    What happens if you just try to Shrink Database?

    Open up SQL Management Studio
    Log in as somebody who has sysadmin rights to the database or whole server
    Find the database, right click and select "Tasks -> Shrink -> Database"

    When prompted, just click OK.

    This should shrink both the database and the transaction logs in one foul swoop.  If you find that this has helped you, then add a shrink database action to your full backup (not transaction log backup) maintenance task.

     Shrink Database
    LVL 68

    Expert Comment

    If it's Main_Multibrand, the log is not being resused because it's preserving some log recores for replication.

    If you still need the replication, you will have to either allow the replication to catch up itself or just turn off replication, shrink the log, turn repl back on, and re-sync the replicated copy from the start with a new db backup.

    DON'T use the existing db backup to re-sync replication -- the existing backup will restore the log file to its current size.

    Also, change the log file growth to a fixed amount, say 100M, rather than 10%.  That's always best, but even more so when the log is so huge, because you don't want to pause the db while SQL formats 25G of log space!

    Author Comment

    Hi Guys,

    Thanks for the quick responses.

    Things are starting to look better. I ahve gone through using the shrink command on some of the less critical databases and am noticing a big difference on some of the DB's.

    For the DB Main_Multibrand I am happy to break the replication on it as I am running out of disk space on our backup server and need to reduce this file size. What would you deem the safest way to stop the replication?



    Author Comment

    Ok I have dropped the replication on the DB Main_Multibrand and the files are as follows

    MDF = 500Mb
    LDF = 233GB

    MDF = 424Mb
    LDF = 226GB

    There are no Open Transactions in the Database and the log_reuse_wait_desc = LOG_BACKUP. I will wait for the next full backup and see what that does and then update this again.



    LVL 68

    Expert Comment

    Once you have replication off, you can put the db in SIMPLE mode temporarily.

    Then you should be able to shrink the *log* -- DON'T shrink the data files, *just* the *log* file.

    USE <database_name>

    DBCC SHRINKFILE ( logical_log_file_name, 100 )

    For performance, you also don't want to have too many VLFs.  So run this command:


    If that returns more than, say, 60, shrink the log as much as possible and then re-alloc the log space all at once:

    DBCC SHRINKFILE ( logical_log_file_name, 1 )

    ALTER DATABASE <database_name> MODIFY FILE ( NAME = logical_log_file_name, SIZE = 100MB )

    LVL 68

    Expert Comment

    Then, you can put the db back in FULL mode, take a full db backup, and you should be good from then on.

    Author Comment

    Thanks for the help Scott.

    For the Main multibrand database the MDF file is now 466MB and the LDF is 600MB and queries over the DB are noticably faster.

    The full back for the DB is now 420MB. Is there a way of sense checking that the transaction logs backed up hourly are correct to still be between 300Mb and 800Mb?


    LVL 68

    Accepted Solution

    Great, glad it helped!

    You could put an alert or some other code to check that, but it really shouldn't be necessary.

    Just make sure that the log backups actually do occur, and you should be fine.

    If you haven't yet, do check the number of VLFs:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now