Post reply

Managing Transaction Logs

  • October 30, 2008 at 10:16 pm

    #195310

    Comments posted to this topic are about the item Managing Transaction Logs

    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

  • October 30, 2008 at 10:22 pm

    #892157

    When do checkpoints happen? Are they automatic? Can they be invoked programmatically?

    Besides simple recovery, are there other techniques to minimize the size of the transaction log?

    Thank you,

    Jake

  • October 30, 2008 at 10:57 pm

    #892163

    Excellent discussion... Useful one

  • October 31, 2008 at 12:50 am

    #892172

    Hi Gail

    Nice article... presented in very easy to understand manner

    One question may not be entirely related to this topic

    Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?

    Thanks

    "Keep Trying"

  • October 31, 2008 at 1:50 am

    #892178

    100% demystification 😎

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • October 31, 2008 at 2:34 am

    #892188

    Jake (10/30/2008)

    When do checkpoints happen?

    At regular intervals depending on the number and frequency of data modifications.

    Are they automatic?

    Yup.

    Can they be invoked programmatically?

    Yes. The command is CHECKPOINT. It's not something that's often needed/

    Besides simple recovery, are there other techniques to minimize the size of the transaction log?

    Keep transactions short. The tran log can only be truncated to the beginning of the oldest active transaction. If you have transactions that remain open for long periods of time, it will prevent log truncation.

    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

  • October 31, 2008 at 2:38 am

    #892190

    Chirag (10/31/2008)

    Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?

    The query processor can only operate on pages in memory. If a query (read or change) requires a page that isn't in memory then the query processor requests that page and puts the query that needs it into a waiting state. The storage engine will go and fetch that page (issuing the appropriate IO requests to the OS), put it into memory and then signal the query processor that the page is there. The query that needed it will then be made runnable and can continue processing.

    Bit of an over-simplification, but serves to explain.

    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

  • October 31, 2008 at 2:41 am

    #892191

    Anirban Paul (10/30/2008)

    Excellent discussion... Useful one

    ALZDBA (10/31/2008)

    100% demystification 😎

    Thank you. Glad you liked it.

    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

  • October 31, 2008 at 2:46 am

    #892192

    GilaMonster (10/31/2008)

    Chirag (10/31/2008)

    Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?

    The query processor can only operate on pages in memory. If a query (read or change) requires a page that isn't in memory then the query processor requests that page and puts the query that needs it into a waiting state. The storage engine will go and fetch that page (issuing the appropriate IO requests to the OS), put it into memory and then signal the query processor that the page is there. The query that needed it will then be made runnable and can continue processing.

    Bit of an over-simplification, but serves to explain.

    Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?

    "Keep Trying"

  • October 31, 2008 at 2:59 am

    #892200

    Chirag (10/31/2008)

    Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?

    Exactly the same. The query processor can only operate on pages in memory. The query processor has no knowledge of the disks and no ability to interact with the disks. That's what the storage engine does.

    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

  • October 31, 2008 at 4:54 am

    #892232

    GilaMonster (10/31/2008)

    Chirag (10/31/2008)

    Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?

    Exactly the same. The query processor can only operate on pages in memory. The query processor has no knowledge of the disks and no ability to interact with the disks. That's what the storage engine does.

    Thank you once again.

    "Keep Trying"

  • October 31, 2008 at 5:10 am

    #892238

    Outstanding! If only BOL had more of this kind of informative and understandable overview material...

    (How did you get so smart, Gail?)

  • October 31, 2008 at 5:59 am

    #892253

    Good stuff here about logs! Nice article Gail! 😎

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • October 31, 2008 at 6:22 am

    #892260

    Nice Article Gail, I did have one question though. Perhaps it's just me but it's been something I've seen here at SSC a few times in recent months...

    what was your last sentence supposed to say? All I see is

    I hope this has clarified some of the details of what the

    Again nice concise explanation of the log.

    thanks.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • October 31, 2008 at 6:39 am

    #892271

    A great article.

    One question about transaction log. I have my databases in full recovery and I don't run backup logs. Every night I do a full backup and only if this step is right then I truncate the backup log.

    Never mind a data lose between full backups.

    I always have thought that if the db is broke then I can run a backup log, after that to restore the full backup from the last day and at the end to restore this last log backup.

    What do you think about this method?

    Many thanks

Viewing 15 posts - 1 through 15 (of 128 total)

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