Post reply

Understanding Transaction log sizes

  • January 7, 2015 at 7:13 am

    #314334

    I have a SQL Server database that is 5 GB. The transaction log seems to keep growing. It is 1.4 GB currently. I actually detached the database to get it back to the initial size hoping the Transaction Log backups I have scheduled every 2 hours would keep it in check. No go.... it grew back to it's current 1.4 GB. I decided to run the TLog backups every hour.... that just blew off my backup drive being as the size of the backups stayed at 1.4 GB. Any advice would be greatly appreciated! I must be missing something.

    Charlie

  • January 7, 2015 at 7:19 am

    #1768442

    Take a read through this, post back if you need help with any specifics

    http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2015 at 8:04 am

    #1768462

    You can't shrink a log by detaching a DB. It sounds like you just need basic log mgmt. training.

    I've got a session where I talk about basic log mgmt. and how backups work.

    You can find it here:

    http://midnightdba.itbookworm.com/EventVids/SQLSatBatonRougeUnderstandBackups/SQLSatBatonRougeUnderstandBackups.wmv

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 7, 2015 at 8:12 am

    #1768466

    I actually tried to shrink the Log file.... no go.... then set to SIMPLE.... then shrink.... no go...... then issue a CHECKPOINT..... no go.... so I detached.... renamed log file.... reattached.

  • January 7, 2015 at 8:25 am

    #1768473

    If the log won't shrink there's a reason. Here's how you find out why:

    select name, log_reuse_wait_desc from sys.databases

    That'll tell you what it's waiting for to be able to shrink.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 7, 2015 at 8:41 am

    #1768480

    rummings (1/7/2015)

    I renamed log file.... reattached.

    Russian roulette with a database.... Seriously don't do that.

    Read through the article I posted please, it describes how to find why the log is not being reused and then goes into some detail on the various reasons.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2015 at 12:58 pm

    #1768584

    Hi Gail.... thanks for the article. I read it and I think the section that applies to my issue is:

    If this log reuse wait reason appears, check that the log backups are correctly scheduled and are succeeding.

    The log backups are occurring every 2 hours successfully. I do not understand the "correctly scheduled" part.... how do you know the correct schedule? I changed the schedule from 2 hours to 1 hour hoping it would decrease the size of the transaction log backups... but they stayed the same size and blew off my backup drive.

    Thanks again to all that replied to my post!

    Charlie

  • January 8, 2015 at 1:35 am

    #1768687

    rummings (1/7/2015)

    I do not understand the "correctly scheduled" part.... how do you know the correct schedule?

    The schedule you're using. So if you have them scheduled every 2 hours, are they actually running every 2 hours?

    So your log reuse wait is log backup and only log backup, and you never see any other one?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 8, 2015 at 6:18 am

    #1768757

    "Log backup" is the wrong way to look at it. It's what SQL is waiting on to be able to truncate the log.

    That may be a backup. It may also be repl or an open xact.

    However, since log backups are the only ones that truncate the log then other backups wouldn't have anything to do with this.

    But don't think of it as a log backup indicator.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 8, 2015 at 7:17 am

    #1768775

    select name, log_reuse_wait_desc from sys.databases

    WHDProdLOG_BACKUP

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • January 8, 2015 at 8:18 am

    #1768790

    Ok, and what method are you using to check log size?

    Use DBCC SQLPERF(LogSpace)

    and post the results for that DB.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 8, 2015 at 8:20 am

    #1768791

    WHDProd2408.30577.051090

  • January 8, 2015 at 9:02 am

    #1768814

    Ok so it's 77% full it look like... now take a log backup then run it again and post those results.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 13 posts - 1 through 12 (of 12 total)

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