Post reply

Question about transaction log.

  • May 20, 2014 at 10:08 pm

    #293478

    I was reading about delay transaction log, durability, truncate and all that, someone on one of the documents mention that a service shutdown doesn't really commit the dirty pages, so I wonder is there a instance wide command that can persist all ongoing transaction logs?

    Or did I miss understand the document.

  • May 20, 2014 at 11:51 pm

    #1715222

    You mean like checkpoint?

    It is not an instance wide command though.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 21, 2014 at 2:09 am

    #1715239

    enriarg (5/20/2014)

    I was reading about delay transaction log, durability, truncate and all that, someone on one of the documents mention that a service shutdown doesn't really commit the dirty pages, so I wonder is there a instance wide command that can persist all ongoing transaction logs?

    I suspect there's a slight misunderstanding here. Dirty pages are not log.

    The transaction log records are written to disk when a transaction commits, always (well, sometimes sooner). The changed data pages (dirty pages) however aren't necessarily written to the data file. They will be at some later point via the checkpoint or lazy writer background processes.

    A service shutdown will try to checkpoint the databases (clean shut down), but can't always (imagine a power failure). If a database wasn't shut down cleanly (all dirty data pages written back to the data file), then when SQL restarts it runs crash recovery, replaying the transaction log records against the data file to redo those changes.

    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

  • May 21, 2014 at 6:27 am

    #1715306

    oh I am aware, sorry I guess I write this a bit to late at night to make sense, still you guys did answer my doubts about the service shutdown and not so clean shutdown.

    I appreciate the help

  • May 21, 2014 at 6:31 am

    #1715310

    In addition to above, when sql server started you may also see the messages for rollback transaction and rollforward transactions in the database. which is nothing but based on checkpoint and dirty pages.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

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

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