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
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.