Track VLF Usage with usp_VLFTracker
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