SQL Server 2008/5 Database-Writing-Concept when Recovery-Mode Full

  • Hi there,

    I was discussing with a collegue the different concepts of database-systems to write updates/insert into the database when Recovery-Mode is Full on sql server 2008.

    My Opinion has been the following:

    - You make an Update on a Table -> Update is written into logfile. The Database writes the actual change physically from the logfile when the logfile is backed up or shrinked.

    - Select on the table means then -> Reading the physical table + reading the logfile for changes

    My collegues opinion:

    - You make an Update on a Table -> Update is written into logfile + into (ram-)buffer of the database like in sql server 2000. The Database writes the actual change from the buffer physically recurringly (e.g. checkpoints) or when buffer is required from new changes.

    - Select on the table means then -> Reading the physical table + reading the buffer for changes

    Can you enlighten us, which is the accurate way?

    my, his or a third one?? 🙂

    Cheers

    Mitch

  • You colleague is closer to correct, but he's still wrong in one place.

    Regardless of recovery model, SQL follows this method for reading/writing. (this has not changed fundamentally since SQL 2000)

    Update/insert/delete. Changes are made to the pages in memory. At the point that the transaction is complete, the record of the changes will be written into the log file, on disk. The changed data pages are written to disk at a later point by one of two operations - checkpoint or lazy writer.

    Select. Reads the data from the pages in memory. If the page required is not in memory, request the storage engine to fetch it, then, once it is in memory, read it.

    The query processor never reads or writes directly to disk. It's always operating on the data pages in memory, in the buffer pool. It's the storage engine that's responsible for getting pages into memory if they're needed but not there.

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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