Post reply

How many log files can a database have?

  • November 6, 2006 at 3:47 pm

    #174837

    In my memory, a database can only have one log file. However, I just saw one database containing 2 log files! Any input will be greatly appreciated.

  • November 6, 2006 at 3:55 pm

    #670198

    You can have multiple log files.  If I remember correctly, if the first log file fills up, then the second one will be used. 

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • November 7, 2006 at 11:41 am

    #670353

    I ran out of space at file 1069...  But I guess this is just enough of what you need to know :

    USE Master

    GO

    CREATE DATABASE MaxLogCnt

    GO

    DECLARE @i AS INT

    DECLARE @sql AS VARCHAR(8000)

    SET @i = 1

    WHILE @i <= 1250

     BEGIN

      SET @sql = 'ALTER DATABASE MaxLogCnt ADD LOG FILE (NAME = MaxLogCnt' + CONVERT(VARCHAR(10), @i) + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL$DEVELOPPEMENT\Data\MaxLogCnt' + CONVERT(VARCHAR(10), @i) + 'log.ldf'', SIZE = 1MB,  MAXSIZE = 100MB,  FILEGROWTH = 1MB)'

      PRINT @sql

      EXEC (@SQL)

      SET @i = @i + 1

     END

    GO

    --DROP DATABASE MaxLogCnt

    GO

  • November 7, 2006 at 12:06 pm

    #670363

    Max files per DB (from BOL) : 32767

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

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