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 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @MikhailFirsov-1277,
After some tests on SQL Server 2014,2016,2017 and 2019, it seems there are only records in the msdb.dbo.logmarkhistory table of SQL Server 2017, after committing the marked transaction.
For SQL Server 2014,2016 and 2019, the msdb.dbo.logmarkhistory table is empty.
SQL Server 2017 CU24:
97015-01.jpg

SQL Server 2014 SP3 CU4 GDR:

97016-02.jpg



SQL Server 2016 SP2 CU17:


97040-03.jpg

SQL Server 2019 CU10:
97065-04.jpg

I’m not sure if it is a known issue, suggest you to submit it to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server. Your feedback is valuable for us to improve the level of products and service. Appreciate for your understanding.
And you also can submit a feedback on the bottom of the doc.
97030-05.jpg



If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



01.jpg (97.3 KiB)
02.jpg (54.9 KiB)
03.jpg (60.4 KiB)
04.jpg (64.7 KiB)
05.jpg (22.8 KiB)
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.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

It should work if you remove the GO statement inside the transaction:

 USE AdventureWorks2019;  
 GO 
    
 BEGIN TRANSACTION CandidateDelete  
     WITH MARK N'Deleting a Job Candidate';  
     
 DELETE FROM AdventureWorks2019.HumanResources.JobCandidate  
      WHERE JobCandidateID = 13;  
     
 COMMIT TRANSACTION CandidateDelete;  
 GO  
    
 SELECT * FROM [msdb].[dbo].[logmarkhistory];
 GO
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.

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

"And that is in your textbook? " - no, it was in the "additional reading" section:

96863-00.png

"but it is definitely not a commonly used feature." - hm... if I recall it correctly mark transactions were also part of my ~Administering SQL Server MS official curriculumbut maybe it was just for the ...mmm ~wide education :)

By the way, my first thought was 'Why did MS (in the msdn example posted above) place GO under the BEGIN TRANSCTION - my textbook does not contain ANY 'Begin transaction...' example with the GO after Begin Transaction - as far as I understand it is not correct - at least in terms of the variables defined BEFORE the BEGIN TRANSCTION - they will be out of scope after this GO.

Nevertheless, without GO the result is the same:
96827-01.png
96835-02.png
96882-03.png



00.png (76.6 KiB)
01.png (26.3 KiB)
02.png (97.8 KiB)
03.png (48.5 KiB)
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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

"but it is definitely not a commonly used feature." - hm... if I recall it correctly mark transactions were also part of my ~Administering SQL Server MS official curriculumbut maybe it was just for the ...mmm ~wide education :)

What is in the official curriculum is one thing. What is used in practice is another. I did an MCP test many years ago, because my company needed to have a few MCPs to be able to retain the Gold Partner award. I believe one of the questions related to WITH CHECK OPTION for views. That one probably had me stumped, because I had never used that option - and over 15 years later, I still haven't.

By the way, my first thought was 'Why did MS (in the msdn example posted above) place GO under the BEGIN TRANSCTION - my textbook does not contain ANY 'Begin transaction...' example with the GO after Begin Transaction - as far as I understand it is not correct - at least in terms of the variables defined BEFORE the BEGIN TRANSACTION - they will be out of scope after this GO.

I would not go as far as saying that it is incorrect to have GO after BEGIN TRANSACTION. As when to have GO and when not to have it - that could be a long novel. In this case it was wrong, because it had the consequence that the mark was not recorded. I will need to say that this is something that surprised me. I can't see anything in the documentation about this, and I don't see any logical reason why it would be that way.

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.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 edited

Thank you all for your help!!!

"That one probably had me stumped, because I had never used that option - and over 15 years later, I still haven't." - the exam I had taken on Administering SQL Server 2012 had many-many questions that stumped me too :)


Regarding the GO: the logmarkhistory table keeps staying empty with or without GO (I've also checked this on my production SQL Server 2012R2 server!) so I think it's a bug and will try - as AmeliaGu-msft suggests - to submit the issue to MS.

Regards,
Michael

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.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered
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.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @MikhailFirsov-1277,
Thanks for your reply.
If the replies could help you, please accept the useful reply as answer to help other community members find the helpful reply quickly.
If you have any other questions, please feel free to let us know.

Best Regards,
Amelia

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

No, no, log marks are recorded on SQL 2019 as well. And, I would assume, all other versions as well, but I did not test them.

However, there is a condition: the database must be in full recovery. Not very strange, because what's the point with recording a log mark in simple recovery?

So I think you need to polish up your Github item, before the writers spend too much time on chasing ghosts. But they could add a line or two about the GO issue.

Here is a repro:

CREATE DATABASE Simple
ALTER DATABASE Simple SET RECOVERY SIMPLE
BACKUP DATABASE Simple TO DISK = 'nul'
go
CREATE DATABASE Fuller
ALTER DATABASE Fuller SET RECOVERY FULL
BACKUP DATABASE Fuller TO DISK = 'nul'
go
CREATE DATABASE FullerGo
ALTER DATABASE FullerGo SET RECOVERY FULL
BACKUP DATABASE FullerGo TO DISK = 'nul'
go
USE Simple
go
CREATE TABLE Habba(a int NOT NULL)
go
BEGIN TRANSACTION Habbe WITH MARK
INSERT Habba(a) VALUES (812)
COMMIT TRANSACTION
go
USE Fuller
go
CREATE TABLE Habba(a int NOT NULL)
go
BEGIN TRANSACTION Habbe WITH MARK
INSERT Habba(a) VALUES (812)
COMMIT TRANSACTION
go
USE FullerGo
go
CREATE TABLE Habba(a int NOT NULL)
go
BEGIN TRANSACTION Habbe WITH MARK
go
INSERT Habba(a) VALUES (812)
go
COMMIT TRANSACTION
go
SELECT * FROM msdb.dbo.logmarkhistory
go
go
USE tempdb
go
DROP DATABASE Fuller
DROP DATABASE FullerGo
DROP DATABASE Simple



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.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @MikhailFirsov-1277,
I am sorry for my mistake. I forgot to backup database after change database recovery model to FULL.
Erland is right. The database should be in Full recovery model, and need to back up after changing from SIMPLE, then there will be records in the msdb.dbo.logmarkhistory table.
Please unmark my answer and edit the feedback. Sorry for the inconvenience.

Best Regards,
Amelia

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.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered ErlandSommarskog edited

Hello all,
''I am sorry for my mistake. I forgot to backup database after change database recovery model to FULL." - no problem, - my AdventureWorks was in the FULL recovery mode but it didn't help. Furthermore, taking full backup did not help either:


97514-q4.png

97468-q12.png

It's only after the LOG backup the mark has been succssefully added:
97533-q10.png
97458-q13.png

At the same time the ErlandSommarskog's code (FullerGO) did work without log backup... rather strange...


Thank you all so much for your help!!!
I'll contact GitLab regarding this issue.



q4.png (109.9 KiB)
q12.png (89.1 KiB)
q10.png (41.3 KiB)
q13.png (33.2 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.

t the same time the ErlandSommarskog's code (FullerGO) did work without log backup... rather strange...

I find that for FullerGo the behaviour is inconsistent. The first time, I run my script, there nothing in Logmarkhistory for FullerGo, the second time there is. Whereas for Fuller there is an entry everytime.

Mysterious.

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.

0 Votes 0 ·