Post reply

Log Bloat

  • January 23, 2007 at 9:33 am

    #68261

    We have a 3.5 GB database (data size) that has a serious case of log bloat - the 4-hourly transaction log backups are at around 15 GB. The database supports an application that is installed many times throughout our subsidiaries, and this is the only instance where we are having this problem. I've tried runing Profiler to see if I can trap where the log activity is coming from, but there doesnt seem to be any pattern at all. The management here isn't happy about buying Log Explorer, Log Rescue or any other per-server licensed product that is only there as a contingent (we have over 60 SQL Servers), so we don't have that as an option.

    Does anyone have any suggestion about how to track down the source of the updates?

     

    Thanks in advance for you help....

  • January 23, 2007 at 3:15 pm

    #685077

    Run sql trace/profiler and perfmon...

    In perfmon use the log file counter from SQLSERVERATABASES performance object...

    Correlate the log file growth with transactions in profiler...

    What is the recovery model? How often do you take log backup?

    MohammedU
    Microsoft SQL Server MVP

  • January 23, 2007 at 3:32 pm

    #685082

    Mohammed,

    thanks for the suggestion. We actually tracked down the error - the application that sits on top of the database couldn't handle a ' in an email address. The application was writing a file into an Image field repeatedly at 1 - 2 minutes intevals The Profiler trace really didnt help at all, the activity appeared to be within normal bounds. The application error logs on the Citrix server were where we actually found the clue to what was going on.

  • January 23, 2007 at 3:39 pm

    #685088

    Simon,

    Recreating/rebuilding indexes is a sure way to bloat transaction logs so I would suggest you make sure that isnt happening, other than that I can only suggest a large amount of data going into and out of the database over the period if the activity on the database is normal then I suggest you grab a copy of Log Rescue/Log Explorer and have a look with that.

    (I dont want this to sound like a sales pitch as its not meant to be ) Log Rescue comes with a free 14 day trail availible from http://www.red-gate.com/products/SQL_Log_Rescue/index.htm so you should be able to track down this problem without buying a license from us - also Red Gate generally do offer volumne discounts/site licenses etc and bundles with our other tools - Log rescue is bundled with SQL Backup which should speed up (and compress) any backups you are doing saving time/disk space.

    Hope this helps,

    - James

    --
    James Moore
    Red Gate Software Ltd

  • January 23, 2007 at 3:40 pm

    #685089

    Ahh replied at the same time as you did. Glad you got your problem solved.

    - James

    --
    James Moore
    Red Gate Software Ltd

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply