Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question

migrated from serverfault.com Jul 29 '11 at 18:13

3 Answers

up vote 3 down vote accepted

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
share|improve this answer
This is more or less the route I ended up taking. – sh-beta Aug 2 '11 at 17:59
up vote 1 down vote

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.

share|improve this answer
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 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! – Max Vernon Oct 4 '12 at 5:58
up vote 5 down vote

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

http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

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

share|improve this answer
Thanks for the script. Chose SqlACID's answer since it included the details in the answer itself and it was basically what your script did. In answer to your question, the verbosity I was referring to was just the raw amount of information returned -- when running this on thousands of database (don't ask), it'd be faster and simpler to be able to retrieve VLF count directly rather than adding DBCC output to a temporary table and counting the rows. An edge case, to be sure. – sh-beta Aug 2 '11 at 18:02
I used that script against 3,000 databases, so I understand your edge case quite well, thanks for the comment. – SQLRockstar Aug 8 '11 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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