Archive
Identifying Object Name for CREATE and ALTER Using fn_dblog()
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.
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
SQL Saturday #256 Slides and Scripts
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.