none
DBCC Loginfo

    Question

  • Log File Growth==> 500 MB          
    total log file size==>11 MB          
                   
    DBCC LogInfo==>            
    FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
    2 253952 8192 15792 0 128 0  
    2 253952 262144 15793 2 128 0  
    2 253952 516096 15787 0 64 0  
    2 278528 770048 15790 0 128 0  
    2 262144 1048576 15791 0 128 1.57E+19  
    2 262144 1310720 15759 0 64 1.57E+19  
    2 262144 1572864 15788 0 64 1.57E+19  
    2 262144 1835008 15789 0 64 1.57E+19  
    2 262144 2097152 15760 0 128 1.57E+19  
    2 262144 2359296 15761 0 128 1.57E+19  
    2 262144 2621440 15762 0 128 1.57E+19  
    2 327680 2883584 15763 0 128 1.57E+19  
    2 327680 3211264 15764 0 128 1.57E+19  
    2 393216 3538944 15765 0 128 1.57E+19  
    2 393216 3932160 15766 0 128 1.57E+19  
    2 458752 4325376 15767 0 128 1.57E+19  
    2 253952 4784128 15768 0 128 1.57E+19  
    2 270336 5038080 15769 0 128 1.57E+19  
    2 253952 5308416 15770 0 128 1.57E+19  
    2 270336 5562368 15771 0 128 1.57E+19  
    2 253952 5832704 15772 0 128 1.57E+19  
    2 335872 6086656 15773 0 128 1.57E+19  
    2 253952 6422528 15774 0 128 1.57E+19  
    2 401408 6676480 15775 0 128 1.57E+19  
    2 253952 7077888 15776 0 128 1.57E+19  
    2 466944 7331840 15777 0 128 1.57E+19  
    2 253952 7798784 15778 0 128 1.57E+19  
    2 253952 8052736 15779 0 128 1.57E+19  
    2 278528 8306688 15780 0 128 1.57E+19  
    2 253952 8585216 15781 0 128 1.57E+19  
    2 253952 8839168 15782 0 128 1.57E+19  
    2 344064 9093120 15783 0 128 1.57E+19  
    2 253952 9437184 15784 0 128 1.57E+19  
                   
    There are 4 VLFs added last time with size =1 GB.      
    As Per paul ==>http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx
                   
    it should be 16 VLFs. Why so.          
    When VLFs Size 1 GB then Why Log file size 11 MB?      

    Manish
    Friday, December 02, 2011 10:46 AM

Answers

All replies

  • Log File Growth==> 500 MB          
    total log file size==>11 MB          
                   
    DBCC LogInfo==>            
    FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
    2 253952 8192 15792 0 128 0  
    2 253952 262144 15793 2 128 0  
    2 253952 516096 15787 0 64 0  
    2 278528 770048 15790 0 128 0  
    2 262144 1048576 15791 0 128 1.57E+19  
    2 262144 1310720 15759 0 64 1.57E+19  
    2 262144 1572864 15788 0 64 1.57E+19  
    2 262144 1835008 15789 0 64 1.57E+19  
    2 262144 2097152 15760 0 128 1.57E+19  
    2 262144 2359296 15761 0 128 1.57E+19  
    2 262144 2621440 15762 0 128 1.57E+19  
    2 327680 2883584 15763 0 128 1.57E+19  
    2 327680 3211264 15764 0 128 1.57E+19  
    2 393216 3538944 15765 0 128 1.57E+19  
    2 393216 3932160 15766 0 128 1.57E+19  
    2 458752 4325376 15767 0 128 1.57E+19  
    2 253952 4784128 15768 0 128 1.57E+19  
    2 270336 5038080 15769 0 128 1.57E+19  
    2 253952 5308416 15770 0 128 1.57E+19  
    2 270336 5562368 15771 0 128 1.57E+19  
    2 253952 5832704 15772 0 128 1.57E+19  
    2 335872 6086656 15773 0 128 1.57E+19  
    2 253952 6422528 15774 0 128 1.57E+19  
    2 401408 6676480 15775 0 128 1.57E+19  
    2 253952 7077888 15776 0 128 1.57E+19  
    2 466944 7331840 15777 0 128 1.57E+19  
    2 253952 7798784 15778 0 128 1.57E+19  
    2 253952 8052736 15779 0 128 1.57E+19  
    2 278528 8306688 15780 0 128 1.57E+19  
    2 253952 8585216 15781 0 128 1.57E+19  
    2 253952 8839168 15782 0 128 1.57E+19  
    2 344064 9093120 15783 0 128 1.57E+19  
    2 253952 9437184 15784 0 128 1.57E+19  
                   
    There are 4 VLFs added last time with size =1 GB.      
    As Per paul ==>http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx
                   
    it should be 16 VLFs. Why so.          
    When VLFs Size 1 GB then Why Log file size 11 MB?      

    Manish
    Friday, December 02, 2011 10:44 AM
  • Hi Manish,

    Can you post the results of the below query?

     

    -- Individual File Sizes and space available for current database
    SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
    FROM sys.database_files;
    

    I want to ask why do you think that your log file is only 11 MB? It may be possible that you are looking at currently how much log file is being used instead of the total size of the log file. Also look at the CreateLSN column for teh DBCC LOGINFO. If a log file grows, CreateLSN remains the same for all the VLFs that were created by that growth. That way you can know how many VLF's are created.

     


    Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/


    Saturday, December 03, 2011 4:22 AM
  • Hello Manish,

     

    Please check this video: - http://blogs.msdn.com/b/sqlserverfaq/archive/2011/06/01/what-are-virtual-log-files-vlf-in-sql-server-video.aspx

    It should answer your question well.

    Kind regards
    Harsh Chawla


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Proposed as answer by Peja Tao Monday, December 05, 2011 5:33 AM
    • Marked as answer by Peja Tao Thursday, December 08, 2011 7:55 AM
    Saturday, December 03, 2011 6:11 PM
  • The information that you posted proves that your question is invalid.  Your most recent log growth shows that the VLF sizes are not what you say they are.  From Kimberly's blog post, (http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx):

    chunks less than 64MB = 4 VLFs

    chunks of 64MB and less than 1GB = 8 VLFs

    chunks of 1GB and larger = 16 VLFs


    Your latest growth shows that it was a < 64MB growth as supported by your total log size being 11MB.  I don't know where you pulled the 1GB from but your values prove that to be incorrect.


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Peja Tao Monday, December 05, 2011 5:33 AM
    • Marked as answer by Peja Tao Thursday, December 08, 2011 7:55 AM
    Sunday, December 04, 2011 7:19 AM