skreebydba

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:

SELECT * FROM fn_dblog(NULL,NULL)

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. */

IF CHARINDEX('2016',@@VERSION) > 0
BEGIN

	DROP TABLE IF EXISTS #logrecords;

END
ELSE
BEGIN

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

		DROP TABLE #logrecords;

	END
END

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

/* Set @tranname to the value you are looking for
   This works for CREATE/ALTER VIEW, CREATE TABLE, and ALTER TABLE
   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 */
SELECT  ROW_NUMBER() OVER(PARTITION BY [Transaction ID] ORDER BY [Current LSN]) AS Row,
    [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
BEGIN

	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;

END

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

CREATE TABLE
ALTER TABLE
CREATE/ALTER VIEW

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.