May 10, 2009 at 11:49 pm
Paul White (5/10/2009)
Lynn,Lynn Pettis (5/10/2009)
I think that is something I'd want to verify before stating.I am wounded - you seem to suggest that I did not verify it...!
Lynn Pettis (5/10/2009)
You can use named transactions, which tells me something has to be written to the transaction log to mark the beginning of a transaction weither named or unnamed.Perhaps you should have verified this before stating? 😛
Lynn Pettis (5/10/2009)
There are also marked transactions, but I know even less about those and would definitely need to read and investigate those more before commenting on them.Very wise.
You may find it useful to run the following demonstration:
use master;
create database [237D8E7D-BDBE-48E5-89C7-874F4D4A054D];
go
use [237D8E7D-BDBE-48E5-89C7-874F4D4A054D];
go
create table dbo.[B49B0C2A-FC9C-45E9-8325-3F710AFF04E8] (a int)
go
checkpoint;
go
begin transaction Bob with mark 'Bob Transaction Start'
-- Just the checkpoint records - no transaction yet
select*
from::fn_dblog(null, null);
-- Generate some log activity
insert dbo.[B49B0C2A-FC9C-45E9-8325-3F710AFF04E8] values (1);
-- Lots of stuff including BEGIN TRAN (operation LOP_BEGIN_XACT, note also the transaction name and description)
select*
from::fn_dblog(null, null);
rollback transaction Bob;
-- Lots more stuff including the ROLLBACK (operation LOP_ABORT_XACT)
select*
from::fn_dblog(null, null);
drop tabledbo.[B49B0C2A-FC9C-45E9-8325-3F710AFF04E8];
go
use master;
drop database [237D8E7D-BDBE-48E5-89C7-874F4D4A054D];
go
You took my statement in the wrong way. I may have worded it incorrectly, but the meaning I had in mind was the I'd want to verify it before stating it. I need to see what is going on behind the scenes before stating that. I didn't mean to imply or infer you hadn't. I am not a SQL Server internals expert, nor claim to be one. Anything I know is from what I have learned from others, or have "guessed at" based on how things work. I have never really taken the time (partly because I haven't had a lot of it) to dig deeper into it.
Of course, you have made it easier by providing me the necessary code without having to figure it out myself, which I thank you for. I had just recently learned that I could query the transaction log, not something I was fully aware of until I had read a post by Gail. I may have read about at sometime, but it never really click until that post. Funny how things sometimes work.
I may have to wait a bit longer to do the research, as I still have soccer going on, plus my oldest daughter is graduating high school shortly, so my plate is full with many other things at the moment.
May 11, 2009 at 12:39 am
Lynn,
Ok - no worries. It seems I did misunderstand you!
Do please run the script I posted when Real World commitments allow - I remember being quite surprised when I first came across this some years ago.
The behaviour is entirely logical and correct once one gives it some thought, but originally I would have expected BEGIN TRAN (with mark) to be logged.
I say it is logical because until something happens which might need to be re-done or un-done from log records, it is pointless recording anything. So, BEGIN TRAN (and any following statements which do not need to be logged - like creating a table variable and inserting into it, for example) is not logged until something 'loggy' occurs afterward.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 11, 2009 at 6:48 am
Paul White (5/11/2009)
Lynn,Ok - no worries. It seems I did misunderstand you!
Do please run the script I posted when Real World commitments allow - I remember being quite surprised when I first came across this some years ago.
The behaviour is entirely logical and correct once one gives it some thought, but originally I would have expected BEGIN TRAN (with mark) to be logged.
I say it is logical because until something happens which might need to be re-done or un-done from log records, it is pointless recording anything. So, BEGIN TRAN (and any following statements which do not need to be logged - like creating a table variable and inserting into it, for example) is not logged until something 'loggy' occurs afterward.
Paul
I knew that table variables and actions done to them were not logged. Once I get a chance I'll be sure to work with the script you provided.
Viewing 3 posts - 91 through 92 (of 92 total)
You must be logged in to reply to this topic. Login to reply