Archive

Archive for the ‘SQL Server 2016’ 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:

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.

CreateAlterView

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.

LockInformationView

 

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

July 13, 2016 Leave a comment

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:

RoutingListResults

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:

RoutingListResultsFixed

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.