1

I have read BOL about marked transactions and I can see contrived examples, but what is a real world use case for MARK'ed TRANSACTIONS?

After reading this I was thinking I would implement marked transactions henceforth in our large batch jobs that run over night. We already use TRANSACTIONS and log backups, but I was hoping to get a use case where this saved time for someone as opposed to just adding it because I just learned about it.

3

Marked transactions make it easy to do point-in-time restores without worrying about actual time. Instead, one restores to logical event.

As a practical example, consider an update that needs to be undone. Now, the update was such that a database restore is needed as wrong value was set to all the columns and original data is lost. Maybe a business analyst set VAT to 24% for all the products instead of a subset. We don't know what the previous VAT values were, so the update cannot be undone.

Restoring the database is simple, but how much of the transaction logs would be applied? At which point-in-time would one stop the log restores? Does the business analyst keep a detailed journal that records the beginning of the update? Maybe all that is remembered is that "it was done Monday afternoon". If there are lots of changes in the database, recovery to right spot is trial and error.

Should the update be done within marked transaction, the DBA will simply query msdb.dbo.LogMarkHistory table and pick symbolic name for the transaction. Log restore is then done with STOPBEFOREMARK = 'VAT update' parameter to stop at the precise point before marked transaction was started.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.