Archive

Archive for April, 2014

Track VLF Usage with usp_VLFTracker

April 24, 2014 Leave a comment

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

SQL Milestones

April 13, 2014 Leave a comment

The past few months have been full of firsts for me.

  • I started working at Apparatus February 17th. I wanted opportunities to learn new things, and I have not been disappointed.
  • Last night, I gave my fourth presentation of the year. With user groups lined up from now through August, I am on pace to meet my goal of averaging one presentation per month this year.
  • March marked the second anniversary of this blog. In that time, I’ve written 61 posts. While this is well short of my initial goal of a post a week, I’m hoping the constant learning opportunities at Apparatus will help me pick up the pace.
  • I hit 1,000 views on this blog for the first time in a month. I am really excited about this, but at the same time, it shows me how much room I have to grow.

It is just about four years since I attended my first SQL Saturday. Thanks to Jes Borland (b|t) for inspiring me to make my voice heard. And it’s just about three years since I attended SQL Skills’ Interals Immersion Event. Thanks to Paul Randal (b|t) and Kimberly Tripp (b|t) for giving me my first opportunity to present on SQL Server outside of work. Finally, I want to thank the members of the SQL community who have taught, helped, and inspired me. There are too many of you to mention, but you’ve made the past four years the most enjoyable and fulfilling of my life.

thankyouhc

Categories: Career, Presenting Tags: , ,