Sql-server – Database is happily available even during log growth

sql servertransaction-log

I’ve been creating a process to go through all our databases and analyze the log structure, backup, shrink, and resize appropriately taking operational requirements and VLF sizing/amount into account. My code is working beautifully, but I encountered something in testing today I wasn’t expecting.

I have a good sized DB with a 104GB log and around 900 VLFs. I backed up the log and shrank down to next to nothing during a period of low activity. I then began to grow it back out to it’s original size in 8GB iterations, keeping VLFs around 512MB. During regrowth (somewhere between 48GB and 56GB), I was curious about DB availability and started running random SELECTS against different tables. Results returned without issue. I then created a table in the DB and looped a bunch of inserts.

I was surprised when the log continued to grow and the table creation and inserts completed without issue or delay. I was under the impression the DB would somehow be locked down, blocking DDL and DML, until regrowth completed. Of course, considering the circular nature of the log, I can see how this would be a false assumption. But I can’t find anything on the web one way or the other.

Can someone shed some light or point me int he right direction on what is going on? Thanks!

Best Answer

I ended up solving my own question with the below code:

    --Using the AdventureWorks2012 database in Full mode with log shrunk to 64MB
    --setup test table
    USE AdventureWorks2012
    go

    IF OBJECT_ID('TestTbl') IS NOT NULL
        DROP TABLE TestTbl
    GO

    CREATE TABLE TestTbl
    (
          RowID          INT IDENTITY(100,100)
        , RightNow       DATETIME
        , SomeField      VARCHAR(255)
        , SomeOtherField VARCHAR(255)    
        , CONSTRAINT PK_TESTTBL_ROWID_RIGHTNOW
            PRIMARY KEY CLUSTERED (RowID, RIGHTNOW)
            WITH (IGNORE_DUP_KEY = OFF)
    )
    GO

    --grow the log out to roughly 50GB and write to the test table in between each grow
    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 1','8193 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 8193)

    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 2','16386 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 16386)

    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 3','24579 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 24579)

    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 4','32772 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 32772)

    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 5','40965 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 40965)

    INSERT TestTbl 
    VALUES (GETDATE(),'GROWING ITERATION 6','49158 MB')

    USE AdventureWorks2012
    ALTER DATABASE [AdventureWorks2012] MODIFY FILE (NAME = N'AdventureWorks2012_Log', SIZE = 49158)


    --in a separate session insert to the test table, i did this after the first 8GB was grown
    INSERT TestTbl
    VALUES (GETDATE(),'SomeValue'+CAST(DATEPART(MILLISECOND,GETDATE()) AS VARCHAR(25)), 'SomeOtherValue'+CAST(DATEPART(MICROSECOND,GETDATE()) AS VARCHAR(25)))
    GO 50000

    --monitor growth iterations
    SELECT * FROM TestTbl WITH (NOLOCK)
    WHERE SomeOtherField LIKE '%MB%'

    --monitor log
    SELECT DB_NAME(database_id) DatabaseName,name LogName,size*8/1024 SizeInMB
    FROM sys.master_files
    WHERE database_id = DB_ID('AdventureWorks2012')
        and type_desc = 'LOG'

    DBCC LOGINFO ('AdventureWorks2012') WITH NO_INFOMSGS

    USE AdventureWorks2012
    GO

    SELECT 
     [Current LSN],
     [Transaction ID],
     [Operation],
     [Transaction Name],
     [Description],
     [CONTEXT],
     [AllocUnitName],
     [Page ID],
     [Slot ID],
     [Begin Time],
     [End Time],
     [Number of Locks],
     [Lock Information]
    FROM sys.fn_dblog(NULL,NULL)


    --modify the rowIDs to span a growth iteration and observe short pause and continue
    SELECT * FROM TestTbl
    WHERE RowID BETWEEN 239500 AND 241000
Related Question