3

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!

  • Are you sure those growths took long enough that you would have noticed any interruption? – Aaron Bertrand Oct 21 '14 at 17:28
  • Were you only person running DML/DDL ? what was number of users connected ?. Personally i dont consider DB with 104 G log size and 900 VLF as in good condition. Specially the VLF's Moreover you said you did not faced blocking while doing DDL and you want to know why you did not is this your question – Shanky Oct 21 '14 at 17:37
  • @Aaron, the log growth was in 8GB increments, so yes, the log growth took a bit to complete for each iteration. – Steve Mangiameli Oct 21 '14 at 20:34
  • @Shanky, the log was unhealthy at 900 VLFs which is why I was going through the process. The log is roughly the same size only now with 212 VLFs, roughly 512MB each. I was the only active user doing any DML/DDL. – Steve Mangiameli Oct 21 '14 at 20:39
  • 1
    Did you actually time it? How did you confirm with 100% certainty that any of these operations actually succeeded while a log growth was actively occurring? The fact that a series of inserts finished shortly after the growth does not necessarily mean they were running unhindered throughout the process. I'm also not sure if the whole 8GB growth blocks or if it happens in chunks. – Aaron Bertrand Oct 21 '14 at 20:41
2

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
  • 1
    Please remove the code from the question and add it here. If it solves your issue, the answer should be in an answer, not in the question. – ypercubeᵀᴹ Oct 22 '14 at 21:39
  • Thanks for the help...still new to the site and figuring things out. I changed the word issue to question, because it was just that - a question. At first I was a bit surprised no one could answer it directly. But I had a hard time convincing my coworkers this was actually occurring without running the code. – Steve Mangiameli Oct 23 '14 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.