Post reply

Multiple Log Files

  • April 5, 2007 at 8:11 am

    #61891

    I have been having a debate with fellow DBA's about whether or not MS SQL Server 2000/2005 will simultaneously take advantage of and write to multiple physical log files for the same database.

    I am of the position that it will only write to 1 log file at a time, though it WILL move the active portion of the log thorugh all physical log files before wrapping around to the beginning of the first physical log file. This is because it wants to write to the log sequentially, which it would not be able to do if writing to more than 1 log file at a time.

    I can't seem to find a definitive answer in BOL or on the net anywhere. Do you know?

    Thanks,

    daustinash

  • April 5, 2007 at 8:24 am

    #698811

    I believe it writes to one at a time, though there are multiple threads, one per file. So if you have a large or open tran, it could be writing to a previous file while new tran information is being written to a second file.

  • April 5, 2007 at 8:29 am

    #698816

    I found this in BOL.

    "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."

    This statement seems to indicate that it uses 1 log file, and writes to it sequentially. I can't get over the fact that writing to more than 1 log file simultaneously, regardless of threading and processes, would require the read/write head on a disk drive to jump back and forth, destroying any benefits of sequential writes.

  • April 6, 2007 at 10:05 am

    #699037

    SQL Server will write to the transaction log sequentially so it writes to only 1 log file at a time.

    Microsoft recommends that you have 1 drive per CPU core for data drives with a single data file on each drive for the database. This way SQL Server can read / write to the database faster because there are multiple entry points into the database when files are spread out across the disks.

    Also they recommend that you maintain a proportional fill for each data file such that the amount of free space in the data files are the same. This is where autogrow can get you in trouble because if the free space changes on only 1 file and SQL Server determines that file has more available free space in it, SQL Server will write to only that file instead of spreading the load across the drives. This create a data hot spot and can adversely affect performance.

    Jason

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

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