question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 commented

Transaction with mark

Hello!

...the last chapter :)

The theory:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-marked-transactions-to-recover-related-databases-consistently?view=sql-server-ver15

"After a marked transaction commits, a row is inserted in the logmarkhistory table in msdb."

The practice from MSDN:

 BEGIN TRANSACTION CandidateDelete  
     WITH MARK N'Deleting a Job Candidate';  
 GO  
 USE AdventureWorks2012;  
 GO  
 DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
     WHERE JobCandidateID = 13;  
 GO  
 COMMIT TRANSACTION CandidateDelete;  
 GO  

96769-q2.png

Q1: Why is the logmarkhistory table still empty after successfull data deletion?


Thank you in advance,
Michael









sql-server-generalsql-server-transact-sql
q2.png (110.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And that is in your textbook? Egads! I think we actually used BEGIN TRANSACTION AT MARK at one of my clients, but it is definitely not a commonly used feature.

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 commented

"By the way, keep in mind that as long as you have no taken a full backup since you switched to full recovery, the database remains effectively in simple recovery." - yes, thanks! Seems this also applies to databases that were restored from the full backups but were never backuped since then: my AdventureWorks2019 was in Full recovery mode by the time I conducted my tests with Begin ... with MARK but logmarkhistory table kept staying empty as if the db were in a simple mode.

I think the whole problem arose because of the two facts:
1) the documentation says that for ... WITH MARK to work the database must be in a full recovery mode meaning you can't have that mode without a full backup - but with the test database (AdventureWorks, for example) you can and
2) at least sometimes taking just a full backup may be not enough - the additional log backup may also be required.

Regards,
Michael

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

2) at least sometimes taking just a full backup may be not enough - the additional log backup may also be required.

That part is mysterious, and I am not sure that the log backup has anything to do with it. Rather it seems that the mark may get lost wen the transaction spans multiple batches.

0 Votes 0 ·

Yes, weird...

0 Votes 0 ·