skreebydba

When SQL Server allocates or grows a transaction log file, it creates a number of virtual log files (VLFs) within that allocation. When a transaction log record is written to a VLF, its status changes from free to active. In SIMPLE recovery model, the VLF will remain active until the next checkpoint is run and no log records are part of active transactions. In FULL recovery model, the VLF will remain active until all transaction log records in the VLF are no longer needed. Log records are needed if they are part of an open transaction, have not been backed up by a transaction log backup, or are needed for mirroring or log replication. You can read more about transaction log architecture here.

DBCC LOGINFO() returns information about each of the VLFs in a database's transaction log, including the status. DBCC LOGINFO() will return a status of 2 for active VLFs and a status of 0 for free VLFs. By tracking VLFs rate of status change, I can get an idea of how much activity is being written to the transaction log.

I needed to determine the rate databases on an instance were generating log records. To do this, I wrote the stored procedure below to track the status of virtual log files in each database log file using DBCC LOGINFO(). I have a job scheduled every 15 minutes to write the results to a table for analysis.

The result set of DBCC LOGINFO() added the recoveryunitid column, making separate temp tables necessary for SQL Server 2008 and SQL Server 2012. I initially tried using conditional logic to create the version-specific schema but found SQL Servedr will not let you run two CREATE TABLE statements for the same object in a script. Thanks to Keith Buck for the suggestion of creating both temp tables and using conditional logic to determine which table to use.

This version uses Aaron Bertrand's (b|t) sp_foreachdb, which is on my short list of the coolest things on the Internet. I highly recommend you use it. If you cannot, the procedure is easily modified to use sp_msforeachdb. Though I really recommend you use it (here are some reasons you should).

/*---------------------------------------------------------------
Created By - Frank Gill
Created On - 2014-04-23

usp_VLFTracker

Procedure dumps the contents of DBCC LOGINFO() to a temp table
for each database on the instance. The output of DBCC LOGINFO()
changed with SQL Server 2012, making two different temp tables
necessary. After the results of DBCC LOGINFO() are gathered for 
each database, they are written to a permanent table for 
analysis.
---------------------------------------------------------------*/


USE yourdbname
GO

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'usp_VLFTracker' 
)
   DROP PROCEDURE dbo.usp_VLFTracker
GO

CREATE PROCEDURE dbo.usp_VLFTracker

AS

	CREATE TABLE #vlf2008
	(fileid INT
	,filesize BIGINT
	,startoffset BIGINT
	,fseqno INT
	,vlfstatus INT
	,parity INT
	,createlsn NUMERIC(25,0))
	
	CREATE TABLE #vlf2012
	(recoveryunitid INT
	,fileid INT
	,filesize BIGINT
	,startoffset BIGINT
	,fseqno INT
	,vlfstatus INT
	,parity INT
	,createlsn NUMERIC(25,0))

	CREATE TABLE #vlfdb
	(dbname SYSNAME
	,currentts DATETIME
	,fileid INT
	,filesize BIGINT
	,vlfstatus INT)
	
	DECLARE @version VARCHAR(20)
	
	SELECT @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)

	IF SUBSTRING(@version,1,2) = '10'
	BEGIN
		
		EXEC sp_foreachdb @command = N' 
		USE ?
		INSERT INTO #vlf2008
		EXEC(''DBCC LOGINFO()'')
		INSERT INTO #vlfdb
		SELECT DB_NAME(), CURRENT_TIMESTAMP, fileid, filesize, vlfstatus FROM #vlf2008

		DELETE FROM #vlf2008
		;'
		
	END
	ELSE IF SUBSTRING(@version,1,2) = '11'
	BEGIN
		
		EXEC sp_foreachdb @command = N' 
		USE ?
		INSERT INTO #vlf2012
		EXEC(''DBCC LOGINFO()'')
		INSERT INTO #vlfdb
		SELECT DB_NAME(), CURRENT_TIMESTAMP, fileid, filesize, vlfstatus FROM #vlf2012

		DELETE FROM #vlf2012
		;'
	
	END
	INSERT INTO youdatabasename..yourtablename
	SELECT dbname, currentts, vlfstatus, COUNT(*) AS vlfcount FROM #vlfdb
	GROUP BY dbname, currentts, vlfstatus
	ORDER BY dbname, vlfstatus

	DROP TABLE #vlf2008
	DROP TABLE #vlf2012
	DROP TABLE #vlfdb
	GO


GO