2012-10-29

VLF count on SQL Server 2012

Some time ago I posted the blog entry „VLF count“ where the (undocumented) function „DBCC LogInfo“ is used to collect the VLF count of a database.
This I have implemented in my SQL Server repository, but with the first SQL Server 2012 installation, the collection failed. The collection is done on a given database with this statement:
SET NOCOUNT ON;
CREATE TABLE #stage(
 [file_id] INT
 ,[file_size] BIGINT
 ,[start_offset] BIGINT
 ,[f_seq_no] BIGINT
 ,[status] BIGINT
 ,[parity] BIGINT
 ,[create_lsn] NUMERIC(38)
);
INSERT INTO #stage EXECUTE (N'DBCC LogInfo WITH no_infomsgs');
SELECT COUNT(1) FROM #stage;
DROP TABLE #stage;


And the error message is:
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.


It turns out that the resultset of DBCC LogInfo has changed with SQL Server 2012, where SQL Server 2000 to 2008 R2 has these DBCC LogInfo columns:
NameType
FileIdINT
FileSizeBIGINT
StartOffsetBIGINT
FSeqNoBIGINT
StatusBIGINT
ParityBIGINT
CreateLSNNUMERIC(38)
And SQL Server 2012 has these DBCC LogInfo columns:
NameType
RecoveryUnitIdINT
FileIdINT
FileSizeBIGINT
StartOffsetBIGINT
FSeqNoBIGINT
StatusBIGINT
ParityBIGINT
CreateLSNNUMERIC(38)

The column „RecoveryUnitId“ is added in the beginning of the resultset.

On SQL Server 2012 this rewritten statement gives the VLF count of the current database:
SET NOCOUNT ON;
CREATE TABLE #stage(
 [recovery_unit_id] INT
 ,[file_id] INT
 ,[file_size] BIGINT
 ,[start_offset] BIGINT
 ,[f_seq_no] BIGINT
 ,[status] BIGINT
 ,[parity] BIGINT
 ,[create_lsn] NUMERIC(38)
);
INSERT INTO #stage EXECUTE (N'DBCC LogInfo WITH no_infomsgs');
SELECT COUNT(1) FROM #stage;
DROP TABLE #stage;


I am rewriting (refactoring) my script to depend on the major version number.

BTW – Please take a look on the Microsoft Connect item „Document DBCC LOGINFO() or create new DMVs to view VLF info“.

No comments: