Archive for the ‘SQL Server Transaction Log’ Category

Identifying Object Name for CREATE and ALTER Using fn_dblog()

August 9, 2016 Leave a comment

Last week, Jes Borland (b|t) asked me a question about the transaction log. Those of you who have read my blog or seen me present know that this is my favorite SQL Server topic. Jes’s question was: “For a transaction with a Transaction Name of CREATE/ALTER VIEW, can the name of the view affected by the CREATE or ALTER statement be identified from the log.”

To check, I ran a transaction log backup on a test database on my system to minimize the number of active transaction log records. I created a view in my test database and ran:


fn_dblog() is a table-based function that returns the active transaction records for the database it is executed against. The two NULL parameters are a Start and End LSN number. Looking at the results, the Transaction Name of CREATE/ALTER VIEW showed up on the LOP_BEGIN_XACT log record.


The next log record for Transaction ID 0000:000007e6 contains an OBJECT_ID in the result set, highlighted below. In the string 9:98099390:0, 9 is the database id of the object’s database and 98099390 is the object id.



This is the object id of the view that was created.  So, Jes’s question was answered.  But this led me to one of my other favorite SQL Server topics: string manipulation.  The following script will identify all transactions for a particular Transaction Name and return the object name affected.  The comments provide additional information about the functionality.

USE YourDatabase;

/* Declare local variables and drop temp table if it exists. */


	DROP TABLE IF EXISTS #logrecords;


	IF OBJECT_ID('tempdb..#logrecords') IS NOT NULL

		DROP TABLE #logrecords;


/* Declare local variables */
DECLARE @tranname NVARCHAR(66);
DECLARE @loopcount INT = 1;
DECLARE @looplimit INT;

/* Set @tranname to the value you are looking for
   Currently researching other possibilities */
SELECT @tranname = 'ALTER TABLE';

/* Get all log records associated with the transaction name specified
   The results contain a row number per transaction, so all occurrences
   of the transaction name will be found */
    [Current LSN], [Transaction ID], [Transaction Name], operation, Context, AllocUnitName, AllocUnitId, PartitionId, [Lock Information]
INTO #logrecords
FROM fn_dblog(NULL,NULL)
WHERE [Transaction ID] IN
	(SELECT [Transaction ID]
	FROM fn_dblog(NULL,NULL)
	WHERE [Transaction Name] = @tranname);

SELECT @looplimit = COUNT(*) FROM #logrecords
WHERE [Transaction Name] = @tranname;

/* The object id for the object affected is contained in the [Lock Information] column of the second log record of the transaction
   This WHILE loop finds the second row for each transaction and does lots of string manipulation magic to return the object id
   from a string like this:
   HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 9:146099561:0
   Once it finds it, it returns the object name */
WHILE @loopcount <= @looplimit

	SELECT TOP 1 @tranid = [Transaction ID]
	FROM #logrecords

	DECLARE @lockinfo NVARCHAR(300);
	DECLARE @startingposition INT;
	DECLARE @endingposition INT;
	SELECT @lockinfo = REVERSE([Lock Information]), @startingposition = (CHARINDEX(':',REVERSE([Lock Information])) + 1), @endingposition = CHARINDEX(':',REVERSE([Lock Information]),(CHARINDEX(':',REVERSE([Lock Information])) + 1))
	FROM #logrecords
	WHERE Row = 2
	AND [Transaction ID] = @tranid;	

	SELECT OBJECT_NAME(REVERSE(SUBSTRING(@lockinfo,(@startingposition),(@endingposition - @startingposition)))) AS ObjectName;

	DELETE FROM #logrecords
	WHERE [Transaction ID] = @tranid;

	SELECT @loopcount += 1;


So far, I’ve tested the script for the following Transaction Names:


It does not work for a DROP, because the object id returned for the Lock Information column no longer exists after the DROP.

Please let me know if you have any comments or questions about the script.

NOTE: The tests that I ran selected from a transaction log containing several hundred records.  In the wild, transaction logs can contain millions of records.  This code will search the entire transaction log and find every occurrence of the Transaction Name you are looking for.  Use caution when running against a production database with a large log.


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


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 

USE yourdbname

-- Drop stored procedure if it already exists
     AND SPECIFIC_NAME = N'usp_VLFTracker' 
   DROP PROCEDURE dbo.usp_VLFTracker



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

	(dbname SYSNAME
	,currentts DATETIME
	,fileid INT
	,filesize BIGINT
	,vlfstatus INT)
	DECLARE @version VARCHAR(20)

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

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

		DELETE FROM #vlf2012
	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


SQL Saturday #256 Slides and Scripts

November 2, 2013 Leave a comment

I had a great time at SQL Saturday #256 in Kalamazoo. Thanks to Josh Fennessy (b|t), Joe Fleming (t), Tim Ford (b|t) and everyone else who put on such a wonderful event. And a big thank you to everyone who attending my session. You can find my slides and scripts here.

Stored Procedure to Delete Rows in Batches Based on a Date Column

August 8, 2013 2 comments

I’ve posted previously about log space reservation (here and here). I’ve also posted about running dynamic T-SQL in a WHILE loop (here). This morning, I wrote a procedure that combines the two topics.

Because SQL Server may have to roll an active transaction back, it reserves space in the log file in addition to the space used to hold the log records. This reserved space is released when the transaction commits.

One of the places I’ve seen this cause problems is a routine that deletes historical data from a table. Imagine you have an application that has been active for two years. It contains 150 million rows and has never been purged. A business decision is made to purge all data older than two months, which accounts for approximately 140 million rows. The simplest way to code this is as follows:

USE [databasename]

DELETE FROM historytable

This will work, but it will consume a huge amount of log space because all 140 million deletes will happen in a single transaction. So, in addition to the log space required for the deletes and any associated allocations, SQL Server will hold on to the reserved space for all 140 million records for the entirety of the transaction. A better solution is to delete records in smaller batches, with each delete wrapped in an explicit transaction.

That brings us to the procedure I wrote this morning for a situation very similar to the one I described above. The procedure will delete rows from a table based on the value of a date column. It allows you to set the number of rows to delete in each batch along with the threshold for deletion. See below for a sample execution.

-- =============================================
-- Create uspBatchDelete
-- Created : 2013-08-08
-- Created By: Frank Gill
-- Performs a batch delete of rows from a table 
-- Inputs - @tablename - the table to delete from
--			@datecolumn - the date column to compare against
--			@increment - the number of rows to delete in each batch
--          @numberofdays - the number of days of data you want to maintain
--			(unless your database has a time machine, the last parm should be negative)
-- =============================================

-- Drop stored procedure if it already exists
     AND SPECIFIC_NAME = N'usp_Batch_Delete' 
   DROP PROCEDURE dbo.uspBatchDelete

CREATE PROCEDURE dbo.uspBatchDelete
	@tablename SYSNAME, 
	@datecolumn SYSNAME,
	@increment BIGINT,
	@numberofdays INT
	-- Declare local variables
	DECLARE @sqlstr NVARCHAR(2000);
	DECLARE @rowcount BIGINT;
	DECLARE @loopcount BIGINT;
	DECLARE @ParmDefinition nvarchar(500);
	-- Set the parameters for the sp_executesql statement
	SET @ParmDefinition = N'@rowcountOUT BIGINT OUTPUT';
	-- Initialize the loop counter
	SET @loopcount = 1;
	-- Build the dynamic SQL string to return the row count
	-- Note that the input parameters are concatenated into the string, while the output parameter is contained in the string
	-- Also note that running a COUNT(*) on a large table can take a long time

	SET @sqlstr = N'SELECT @rowcountOUT = COUNT(*)
    FROM ' + @tablename + ' WITH (NOLOCK)
    WHERE ' + @datecolumn + ' < DATEADD(DAY,' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
	-- Execute the SQL String using sp_executesql, passing in the parameter definition and defining the output variable
	EXECUTE sp_executesql
    ,@rowcountOUT = @rowcount OUTPUT;
	-- Perform the loop while there are rows to delete
	WHILE @loopcount <= @rowcount
		 -- Build a dynamic SQL string to delete rows
		 SET @sqlstr = 'DELETE TOP (' + CAST(@increment AS VARCHAR(10)) + ') FROM ' + @tablename + '
		 WHERE ' + @datecolumn + ' < DATEADD(DAY,' + CAST(@numberofdays AS VARCHAR(4)) + ',GETDATE())';
		 -- Execute the dynamic SQL string to delete a batch of rows
		 -- Add the @increment value to @loopcount
		 SET @loopcount = @loopcount + @increment;
		 PRINT CAST(@increment AS VARCHAR(10)) + ' rows deleted.'
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_PROCEDURE() AS ErrorProcedure
		,ERROR_LINE() AS ErrorLine
		,ERROR_MESSAGE() AS ErrorMessage;


In this example, the procedure will delete rows from tablename in batches of 50,000 where datecolumnname is older than two months. If you are dealing with a scenario similar to the one described above, deleting 22 months of data in a single run may take longer than you want. In that case, you can run the procedure multiple times changing the @numberofdays parm with each run. You will need to do some testing to determine the optimal values for the @increment and @numberofdays parameters.

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.uspBatchDelete @tablename = 'tablename',
@datecolumn = 'datecolumnname', 
@increment = 50000, 
@numberofdays = -60;

I hope you can put this code to use.

SQL Saturday #239 – Another Presentation Under My Belt

July 28, 2013 1 comment

I just got home after a great weekend in Iowa City at SQL Saturday #239. I presented Interrogating the Transaction Log for the fifth time, this time with an extended section on virtual log files. Each time I’ve done the presentation, people have asked questions about VLFs and I always try to give the people what they want. Thanks to Ed Leighton-Dick (t), Sheila Acker (t) and everyone else who put on a great event. And a big thank you to everyone who attended my presentation. You can find the slides and demos here.

P.S. – You can also find the scripts and slides at the SQL Saturday #239 site as well.

SQL Saturday 239 East Iowa

July 14, 2013 Leave a comment

I am going to be presenting at SQL Saturday 239 in Iowa City, Iowa on Saturday, July 27, 2013. I will be presenting Interrogating the Transaction Log. It will be my fourth time giving this presentation and each time I’ve gotten great suggestions, so this should be the best one yet. You can register here and find more information about the schedule here. This will be my fifth SQL Saturday and I’ve learned a tremendous amount and met amazing people at each of them, so I encourage you to attend if you can.

SQL Saturday Madison

April 6, 2013 1 comment

I just finished giving my first presentation at a SQL Saturday. The hour is quite a blur, but based on the evaluations and personal feedback I received it went well. I was really happy with the turnout and there were some great questions.

Thanks to Jes Borland for taking this picture:

I made a point of asking folks to provide feedback and got some good suggestions. One request was to provide mode information about VLF use, so I plan on incorporating the concepts Kimberly Tripp provides here into an update.

The slides and scripts for my presentation are available here.

Many thanks to the folks who organized SQL Saturday Madison and to the attendees of my session. Being a part of the SQL family continues to be a inspiring and invigorating experience.