Post reply

How are logs written?

  • April 13, 2005 at 5:12 pm

    #164170

    Hi everyone,

    If I have two log files in a databases, how does SQL 2000 write to the log files? Does it write to both at the same time if several transactions are running? Thanks.

  • April 13, 2005 at 7:22 pm

    #552564

    From Books Online "Transaction Log PHysical Architecture"

    "When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log, which grows toward the end of the physical log"

    "If the log contains multiple physical log files, then the logical log will move through all of the physical log files before it wraps back to the start of the first physical log file."

    In other words, SQL Server works with one physical log file at a time, moving through it sequentially. 

    Scott Thornburg

  • April 13, 2005 at 7:36 pm

    #552569

    Thank you very much Scott.

    I was wondering the same thing about tempdb. If the tempdb has more than one data file, and there are multiple transactions on the database at a given time, will sql write to each file one by one or to all files at the same time? Thank you again.

  • April 14, 2005 at 12:50 am

    #552608

    It does not work that way. Transactions are written to the log file when they occur, and then written to the data file(s) at 'regular' intervals (during checkpoints). There is nothing special about tempdb versus other databases with this.

    Regarding log files: I do not know of any reason to have more than one log file. Like Scott said, only one will be used at a time, and I know of some problems that can occur if you have more than one.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • April 14, 2005 at 7:47 am

    #552687

    I'll join the club here.

    Since you have no control what is written to which log file, having multiple files will yield you nothing.

     

    ...except even more trouble in case of a disaster.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 18, 2005 at 2:57 pm

    #553279

    Thanks everyone for ur help on this.

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

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