Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.


I don't believe there is another way; you could always do something like this:

   CREATE TABLE #dbcc_out 
                      (FileID varchar(3), 
                       FileSize numeric(20,0),
                       StartOffset bigint, 
                       FSeqNo bigint, 
                       Status char(1),
                       Parity varchar(4), 
                       CreateLSN numeric(25,0))

    INSERT INTO #dbcc_out                  EXEC ('DBCC LOGINFO')

    select count(*)  from #dbcc_out
  • This is more or less the route I ended up taking.
    – sh-beta
    Aug 2 '11 at 17:59

I went and checked the POSH script I had that tied to an Excel spreadsheet:


And you mentioned that DBCC LOGINFO was 'verbose', which makes me want to ask, have you tried using the WITH NO_INFOMSGS option?


You can calculate the number of VLFs you will have.

If your initial log file size is less than 64MB then there will be 4 VLFs, between 64MB and less than 1GB there will be 8 VLFs, and a size of 1GB and larger will have 16 VLFs. The same respective number of VLFs will be created when the log file grows.

So for a 1 GB log file that is set to grow by 1 GB, if the current log file size is 3 GB, you should have 48 VLFs.

  • 1
    All this assumes that the log hasn't been shrunk and grown at funny sizes by other people that you aren't aware about.
    – mrdenny
    Aug 1 '11 at 20:39
  • There are too many assumptions in calculating it this way to be reliable.
    – sh-beta
    Aug 2 '11 at 18:03
  • 1
    -1 since the VLF count is not inherently reliant on the physical log size. I have seen 128GB log files with literally hundreds of thousands of VLFs. Talk about poor recovery performance!
    – Hannah Vernon
    Oct 4 '12 at 5:58

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.