Post reply

Log File VLFs

  • January 11, 2011 at 9:36 pm

    #64317

    Comments posted to this topic are about the item Log File VLFs

  • January 12, 2011 at 12:05 am

    #1272199

    Nice question! Again, I've learned something new about VLF.

    You can test it out with the following script:

    CREATE DATABASE Test_DB

    GO

    USE Test_DB

    GO

    DBCC LOGINFO;

    In the column Status, a status = 2 means active VLF, while status = 0 means inactive VLF.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 12, 2011 at 1:05 am

    #1272215

    today's question is really interesting.

    Thanks,

    Yash

  • January 12, 2011 at 3:24 am

    #1272254

    Good question. In all my google'ing I couldn't get a definite answer, and still couldn't even in the linked article associated with the answer.

    Ended up applying commonsense, and 50/50 with the answer being "1" or "0"... decided if there are no transactions to restore/rollback then there is no need for an active vlf... which, on reflection, went against everything I've learned about computer & software architecture.

    Doh! 🙂

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • January 12, 2011 at 3:29 am

    #1272259

    Nice question Steve.

  • January 12, 2011 at 3:48 am

    This was removed by the editor as SPAM

  • January 12, 2011 at 3:51 am

    This was removed by the editor as SPAM

  • January 12, 2011 at 5:34 am

    #1272305

    Thanks for the question.

    I was about to encite a forum riot with "it depends how big the log is and you didn't say!!!! bad question! bad poster! trick question! gimme muh pointz bak!" (and any additional mind reading comments)

    But I read it a couple more times, correctly, and it all made sense. 😀

    thanks again

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • January 12, 2011 at 6:04 am

    #1272318

    Nice question.

    The fact there's always an active VLF even when the log is empty doesn't appear to be documented anywhere. But it seems like common sense to me.

    Tom

  • January 12, 2011 at 6:07 am

    #1272319

    Tom.Thomson (1/12/2011)

    Nice question.

    The fact there's always an active VLF even when the log is empty doesn't appear to be documented anywhere. But it seems like common sense to me.

    Exactly my thinking.

  • January 12, 2011 at 6:50 am

    #1272338

    That was a good question and I learned a couple of things from it.

    I thought that 4 VLFs would be created and almost answered that way until the word "active" caught my eye. That seemed to be a big hint.

    Then, after running Koen's query, I found that my original guess of 4 VLFs was incorrect and only 2 are created.

  • January 12, 2011 at 6:52 am

    #1272339

    I couldn't help but thinking about the old joke...

    Q. How many people are dead in a cemetary?

    A. All of them.

    😛

  • January 12, 2011 at 6:56 am

    #1272341

    Scott Arendt (1/12/2011)

    That was a good question and I learned a couple of things from it.

    I thought that 4 VLFs would be created and almost answered that way until the word "active" caught my eye. That seemed to be a big hint.

    Then, after running Koen's query, I found that my original guess of 4 VLFs was incorrect and only 2 are created.

    That's assuming you're using the default size of the model database. Manually make the log file something bigger (like 8GB), and you'll see a few more - about a dozen.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 12, 2011 at 7:21 am

    #1272355

    from QOTD of 7th JAn

    log created at:

    less than 64MB - 4 VLFs

    64MB and less than 1GB - 8 VLFs

    1GB or greater - 16VLFs

    ---------------------------------------------------------------------

  • January 12, 2011 at 7:32 am

    #1272364

    Thanks for the question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 20 total)

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