Post reply

Why is my transaction log full?

  • February 22, 2011 at 1:22 pm

    #1290083

    Abi Chapagai (2/22/2011)

    We can also get the specific database with sys.databases with where condition.

    select * from sys.databases

    where name = 'DBName'

    I always use with this condition so that i can be specific on the particular database.

    Thanks,

    What I mean by 'database specific' is that opentran applies only to the database it is run in. You cannot run opentran in DB1 and get output related to DB2. You can do that with sys.databases

    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

  • February 22, 2011 at 4:41 pm

    #1290188

    Robert Davis (2/21/2011)

    david.howell (2/21/2011)

    Thanks for the very informative article.

    Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'

    However, running DBCC OPENTRAN report "No active open transactions."

    This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.

    Any pointers on what to do from here?

    Sys.databases isn't going to be updated as soon as the transaction completes. Try running a checkpoint in the database.

    Thanks 🙂

    I was concerned because I read in BOL that the status 'NOTHING' means that the log is not waiting on anything including open transactions, so I wanted to see 'NOTHING'. I ran

    CHECKPOINT;

    And now it is saying 'NOTHING', although I take it from your reply that my concerns were misguided.

  • February 22, 2011 at 5:08 pm

    #1290190

    Thanks for this article! Funny how something so important and fairly simple can be surrounded by so much obscurity.

    -------------------------------------------------------------------------------------------------
    My SQL Server Blog

  • February 22, 2011 at 7:30 pm

    #1290200

    Thanks for the clarification

  • February 22, 2011 at 7:31 pm

    #1290201

    Thanks you

  • February 23, 2011 at 5:59 am

    #1290323

    Thanks to everyone involved in writing this one. Great article!

    -Ki

  • February 23, 2011 at 8:46 am

    #1290452

    Very informative article. In the section entitled 'Checkpoint' you say 'If the checkpoint log reuse wait appears frequently...'. Where would I look to check if this is appearing frequently?

    Thanks.

  • February 23, 2011 at 9:27 am

    #1290483

    K Foster (2/23/2011)

    Very informative article. In the section entitled 'Checkpoint' you say 'If the checkpoint log reuse wait appears frequently...'. Where would I look to check if this is appearing frequently?

    Thanks.

    From the article:

    Causes of delayed log truncation

    These are the log reuse wait reasons from sys.databases

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • February 23, 2011 at 9:28 am

    #1290485

    Excellent article Gail. You are pro, and pro's lean on other pros. Nice to see such a practical treatment of log reuse while still giving significant background information in such a concise manner.

  • February 23, 2011 at 9:33 am

    #1290490

    Please ignore my question in the previous post - reread the article and found the answer... (insert embarrassed icon here)

  • February 23, 2011 at 9:34 am

    #1290492

    Thank you Mr. Davis.

  • February 23, 2011 at 10:51 am

    #1290541

    Can you elaborate on why long-running backups can result in significant transaction log growth?

    Or at least point to an explanation and example.

  • February 23, 2011 at 11:51 am

    #1290558

    GilaMonster (2/22/2011)

    Abi Chapagai (2/22/2011)

    We can also get the specific database with sys.databases with where condition.

    select * from sys.databases

    where name = 'DBName'

    I always use with this condition so that i can be specific on the particular database.

    Thanks,

    What I mean by 'database specific' is that opentran applies only to the database it is run in. You cannot run opentran in DB1 and get output related to DB2. You can do that with sys.databases

    Thanks for the clarification, and you are right that we cannot run the opentran in one database and get the related result to another database.

  • February 23, 2011 at 9:40 pm

    #1290719

    Chet Xemoka (2/23/2011)

    Can you elaborate on why long-running backups can result in significant transaction log growth?

    Or at least point to an explanation and example.

    From the article:

    The active portions of the log are necessary for database backups, as the backup has to include at least that much of the log to ensure a consistent restore. As such, long-running database backups may result in log growth as the portions of the log necessary for the backup must be retained for the duration of the backup.

    Do you want more than that?

    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

  • February 24, 2011 at 10:01 am

    #1291089

    Gail - Great Article! Appreciated.

Viewing 15 posts - 31 through 45 (of 76 total)

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