A little known and perhaps underappreciated feature of SQL Server is the ability to assign a name and a description to a transaction. The name of the transaction is recorded in the transaction log, and the transaction is called a marked transaction. The syntax is BEGIN TRANSACTION TransactionName WITH MARK 'TransactionDescription' (the description is optional). The MSDN topic for BEGIN TRANSACTION has additional details. Marked transactions for all databases on the instance are also recorded in the dbo.logmarkhistory table in the MSDB database.
As the MSDN topic notes, one of the reasons to use marked transactions is to be able to restore a set of related databases, possibly residing on different SQL Server instances, to a logically consistent state. This could be achieved by using the STOPATMARK or STOPBEFOREMARK arguments of the RESTORE LOG statement. Another reason to use marked transactions, and perhaps a more common one, is to simplify recovery and minimize potential data loss when deploying database schema or data changes, i.e. during a release. The approach is to make all database changes in a marked transaction (the assumptions here are that all changes are transactional, and are sufficiently small to not cause concurrency or resource problems due to a large or long running transaction). If the change causes some unexpected results (also known as the “Oh no!” moment), it would be possible to RESTORE LOG … WITH STOPBEFOREMARK to restore the database to the point just before the erroneous transaction. This would be more accurate than restoring to a timestamp just before the change, which may not even be known.
None of this is really new or groundbreaking, and the main reason for this post is to describe some slightly unexpected results I noticed when testing a database change implemented with a marked transaction. The change script was completing successfully, however no marked transaction was recorded in the dbo.logmarkhistory table. This was puzzling, because I’ve used this approach before and did see the marks in that table. One difference in this case was that the target database was just created. The recovery model of the database was Full, but no full backup was taken yet, which means that the database was using the “pseudo-Simple” recovery model. Apparently, even though a marked transaction can be successfully committed against such a database (and the mark is actually recorded in the log, as seen in the output from fn_dblog()), it is not recorded in the dbo.logmarkhistory table. On reflection, this makes sense – if a database is still using the Simple recovery model, then it is not possible to restore the database to a point in time, or to a log mark, anyway, so recording a marked transaction in the dbo.logmarkhistory table would be misleading. As soon as I took a full backup of the database, marked transaction started appearing in the dbo.logmarkhistory table.
© 2019 Microsoft. All rights reserved.