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.
Fun With READ_ONLY_ROUTING_LIST Syntax
I finally am getting to work with SQL Server 2016 and spent the past two days building out an Availability Group to test the new read-only replica functionality. This allows connections with an ApplicationIntent of ReadOnly to round-robin between all instances in the READ_ONLY_ROUTING_LIST, load balancing the read-only activity.
For my test, I built out three replicas and set all three to be readable secondaries. Using Microsoft's documentation on configuring read-only routing, I coded the following:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG') ) );
To test the new setup, I used the SQLCMD examples from Eric Russo's blog post on Load Balancing 101. Running the following command:
SQLCMD -S SKREEBYLIST -K ReadOnly -d testdb -Q "SELECT @@SERVERNAME"
returned AGNODE2\SKREEBYAG in each case. I could not figure out what I was doing wrong. After much frustration, I noticed the routing check query at the bottom of Eric's post:
SELECT ar_Primary.replica_server_name 'Primary', ar_secondary.replica_server_name 'Route_to', ror.routing_priority 'Priority' FROM sys.availability_read_only_routing_lists ror INNER JOIN sys.availability_replicas ar_Primary ON ror.replica_id = ar_Primary.replica_id INNER JOIN sys.availability_replicas ar_secondary ON ror.read_only_replica_id = ar_secondary.replica_id INNER JOIN sys.availability_groups ag ON ag.group_id = ar_Primary.group_id WHERE ag.name = 'SKREEBYHA' ORDER BY 1,3
Running it returned the following results:
So, in technical terms, something was screwy. Looking at Eric's post again, I noticed his third example for setting a routing list:
ALTER AVAILABILITY GROUP SQL2016AG MODIFY REPLICA ON 'MyNode3' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST= (('MyNode4','MyNode1','MyNode2')) ) )
Notice the second set of parentheses around the READ_ONLY_ROUTING_LIST value. Based on this, I changed my code to:
ALTER AVAILABILITY GROUP SKREEBYHA MODIFY REPLICA ON 'AGNODE1\SKREEBYAG' WITH ( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST=(('AGNODE2\SKREEBYAG','AGNODE3\SKREEBYAG')) ) );
and, voila, the routing list check query returned the following:
Rerunning the SQLCMD test, the read-only connections round-robined between AGNODE2 and AGNODE3.
Many thanks to Eric Russo (b|t) for a great blog post. I hope this saves you some frustration when working with read-only routing.