SQL Server Transaction Marks and Names

In the past I have normally just used basic SQL Server transactions. BEGIN TRAN – update some data, then COMMIT or ROLLBACK depending on the outcome.

Whilst studying for exam 70-461 I noticed a number of the questions talking about marking transactions, and naming them as well, so this article is to clarify the procedures of those types of transaction commands.

How To Name A Transaction

To name a transaction you just put the name after it.

Transaction Naming Example

------------------------------------------
BEGIN TRAN Fireball

CREATE TABLE #Tmp (id INT)

DROP TABLE #tmp
ROLLBACK TRAN 
------------------------------------------

Note: Even if you did not specify the name, it will still rollback the open transaction should you specify the ROLLBACK command.

There does not seem to be a particularly good reason as to why you should name them, rather just for easier logic and identifying which transaction is which.

How To Mark A Transaction

Marked transactions are those where a mark is placed at a certain point. This is often used with distributed transactions where a consistent state is
maintained across more than one database.

You will see the odd question pop up related to these in the 70-461 exams.

Example Of Transaction Mark

------------------------------------------
BEGIN TRAN Fireball

CREATE TABLE #Tmp (id INT)
INSERT INTO #tmp SELECT 3
UPDATE #tmp SET id = 4

BEGIN TRAN Fireball2 WITH MARK

DROP TABLE #tmp
ROLLBACK TRAN Fireball2
------------------------------------------

In the code above it does not create a second transaction, it just renames the first one to the the name Fireball2 with a mark.

To spread the marks to other servers you need to create stored procedures on each of the servers that create a mark, then run all of them within one transaction. Here is a MS Article for further info on distributed mark.

When you restore the dbs, you can restored them to the mark in the Transaction log.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.