Logged Operations

Question

Logged Operations

Viewing 15 posts - 46 through 60 (of 89 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

SanDroid (4/7/2011)

GSquared (4/7/2011)
Diagram it out on a piece of paper. Just a simple flow-chart. You'll quickly see...
Wish Flow charts where better here.
Temp Tables are logged --> MARS support docuemtation says Transactions in Temp Tables are supported by the Version Store --> This is Data in the Version store that is logged.
I agree that is most likely off, but it is as valid as your two cents about my point.
All the supporting documentaion anybody has provided are Blogs that state "operations in the version store are not logged". Not data, operations.
I Look forward to any actual documentation someone can point to that says data affected in the version store is not logged....
I would love to read it. I spent an hour looking for it before my first post today.
How much time do you put into researching your post?

Please provide documentation on what operations in the version store do not involve data. Separating the two the way you are trying to is nonsense. You're setting up a strawman based on a non-existent semantic difference that you know perfectly well is incorrect. Do you really think that operations on the version store are being done on something other than data in the version store? No, of course you don't. You're smarter than that. Swallow a little pride, accept that you got the answer wrong, learn from it. Simple.

It was also disingenuous of you to claim that Wayne would agree with you and covertly change the question/answers earlier in this thread. That aspersion on your part is something I only noticed after responding to the other bit, and it really isn't worthy of a professional forum.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 2

SanDroid (4/7/2011)

Hugo Kornelis (4/7/2011)
I don't have the time to really dive into this at the moment, and I'm also sitting at a computer that is running SQL Server 2005 only so I can't try - but can you perhaps post some code or other evidence to demonstrate that data in the version store can indeed be in the log file?
Hugo -
Why do I need to show something being written to a physical log file to clarify this?
Are updates to temp tables logged written to a physical log file?
We all agree that they are logged becuase they can be rolled back on error.
Trigger updates and inserts are supported by the version store.
They are also rolled back on an error. I watch this happen all day long.
So... ?
Also Hugo... Not certain what you meant about SQL 2005.
SELECT * from sys.dm_tran_version_store returns rows on every SQL 2005 server I can connect to.

Again, NONE of that is the data in the version store. That's data in temp tables, et al. Not data in the version store itself. Stop conflating the two.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

WayneS (4/7/2011)
CirquedeSQLeil (4/7/2011)
In an effort to clarify - a request has been sent to Paul Randal on the subject.
FYI, Paul has responded (via Twitter):
"Tell them I said all vstore activity is nonlogged, but updates that cause vstore activity are fully logged of course"
He also says that a better choice for the question would have been: "changes to the version store in tempdb", because DML that generates version has to change user DB and version store.

Most Excellent

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

April 7, 2011 at 3:00 pm

#1308846

Excellent!

My blog

SanDroid SSChampion Points: 10068 More actions · Answer 5

CirquedeSQLeil (4/7/2011)
In an effort to clarify - a request has been sent to Paul Randal on the subject.

FYI, Paul has responded (via Twitter):
"Tell them I said all vstore activity is nonlogged, but updates that cause vstore activity are fully logged of course"

Thank you Paul finally for making that clear.

That is exactly what I was trying to point out initialy, but since I was not on the SQL development team at MS I could not make it as clear as that.

I seriously appreciate WayneS for following through like this. It is Awesome and not what one would expect from most QOTD authors.

I seriously do NOT appreciate those that posted incorrect assumtions against my statements and tried to constru my point by adding to it or mis-quoting it. It is these types of things that start the wild Logging Myths in the first place.

Someone please twit Paul and ask him where I can send the six pack of his choice?:w00t:

Obviously send me the data in a private message.:-P

SanDroid SSChampion Points: 10068 More actions · Answer 6

GSquared (4/7/2011)
SanDroid (4/7/2011)
SELECT * from sys.dm_tran_version_store returns rows on every SQL 2005 server I can connect to.
Again, NONE of that is the data in the version store. That's data in temp tables, et al. Not data in the version store itself. Stop conflating the two.

SELECT * from sys.dm_tran_version_store

I totally do not understand your post. This is the transaction version store DMV. I never mentioned the TempDB?!?!

TomThomson SSC Guru Points: 104773 More actions · Answer 7

CirquedeSQLeil (4/7/2011)
I'll put it out there that I missed it because I went with the none option (meaning all are logged based on question phrasing). I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D

Exactly what I did. So it seems a good question to me, as I learnt something new.

Tom

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

Tom.Thomson (4/7/2011)
CirquedeSQLeil (4/7/2011)
I'll put it out there that I missed it because I went with the none option (meaning all are logged based on question phrasing). I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D
Exactly what I did. So it seems a good question to me, as I learnt something new.

I totally agree with Tom. This question was excellent! The whole point of the QOTD is to learn something new and with this question I'm sure many of us just did that. I personally like the way SanDroid persist until he got the answer he was looking for(a bit to eager :-P), but we should all be eager to learn/ clarify things we don't fully understand.

Wayne, I will like to thank you for the question, do you have more questions like this? 😀

My blog

TomThomson SSC Guru Points: 104773 More actions · Answer 9

SanDroid (4/7/2011)

GSquared (4/7/2011)
Diagram it out on a piece of paper. Just a simple flow-chart. You'll quickly see...
Wish Flow charts where better here.
Temp Tables are logged --> MARS support docuemtation says Transactions in Temp Tables are supported by the Version Store --> This is Data in the Version store that is logged.
I agree that is most likely off, but it is as valid as your two cents about my point.
All the supporting documentaion anybody has provided are Blogs that state "operations in the version store are not logged". Not data, operations.
I Look forward to any actual documentation someone can point to that says data affected in the version store is not logged....
I would love to read it. I spent an hour looking for it before my first post today.
How much time do you put into researching your post?

In what follows for read "modify" to include "delete" and "insert".

I think you are confused about what logging is. The transaction log contains log records that record operations; the only reason that there is any data in those records is that if the operation modified data that data modification is part of description of the operation and has to be logged (so that if it is still in the log when recovery takes place then if it has been committed it can be rolled forwards, and if it hasn't it can be rolled back). That data didn't affect the version store in any way - it was the operation that modified the data that caused a version store operation, not the data itself. So its no good claiming that because that data was logged some data that affected the version store was logged, because it was the modification - not the data - that caused a version store operation.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 10

SanDroid (4/7/2011)

Hugo Kornelis (4/7/2011)
I don't have the time to really dive into this at the moment, and I'm also sitting at a computer that is running SQL Server 2005 only so I can't try - but can you perhaps post some code or other evidence to demonstrate that data in the version store can indeed be in the log file?
Hugo -
Why do I need to show something being written to a physical log file to clarify this?
Are updates to temp tables logged written to a physical log file?
We all agree that they are logged becuase they can be rolled back on error.

Yes of course they are written to the transaction log file; I can very easily write a bunch of programs to declare and initialise hordes of table variables and then initiate long running transactions in which those table variables are modified, and ensure that I run enough copies so that the information about the states to which the table variables must be returned on error are too big to fit into RAM.

If you don't believe it, take Paul Randall's debunk code for TRUNCATE and modify it to handle table variables instead - you'll see that the log file contains the records.

Tom

SanDroid SSChampion Points: 10068 More actions · Answer 11

Tom.Thomson (4/7/2011)

Yes of course they are written to the transaction log file; I can very easily write a bunch of programs to declare and initialise hordes of table variables and then initiate long running transactions in which those table variables are modified, and ensure that I run enough copies so that the information about the states to which the table variables must be returned on error are too big to fit into RAM.
If you don't believe it, take Paul Randall's debunk code for TRUNCATE and modify it to handle table variables instead - you'll see that the log file contains the records.

Tom,

Is a Table Variable an a Temp Table the same thing?

It was my understanding that Table Variables and #Temp Tables store and execute data differantly. Expecially in how page allocation are made in SQL 2008. That is why so many of us still use #Temp instead of declaring a Table Variable.

I am not saying that your statement about how to test this is wrong. It could be.

I am saying that a Table Variable is not a Temp Table and they work differently.

If you want some links to back that up please request in a private message and I will try to get to you tommorow.

My fingers refuse type any more today.

SanDroid SSChampion Points: 10068 More actions · Answer 12

Ignacio A. Salom Rangel (4/7/2011)
I personally like the way SanDroid persist until he got the answer he was looking for(a bit to eager :-P), but we should all be eager to learn/ clarify things we don't fully understand.
Wayne, I will like to thank you for the question, do you have more questions like this? 😀

Ingnacio,

I apreciate that. In know you were one of the first ones to respond and try to point out that there was a reference to the answer.

It is good to know at least three people understood what I wanted, and what was responses to other people.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

SanDroid (4/7/2011)

Tom.Thomson (4/7/2011)
Yes of course they are written to the transaction log file; I can very easily write a bunch of programs to declare and initialise hordes of table variables and then initiate long running transactions in which those table variables are modified, and ensure that I run enough copies so that the information about the states to which the table variables must be returned on error are too big to fit into RAM.
If you don't believe it, take Paul Randall's debunk code for TRUNCATE and modify it to handle table variables instead - you'll see that the log file contains the records.
Tom,
Is a Table Variable an a Temp Table the same thing?
It was my understanding that Table Variables and #Temp Tables store and execute data differantly. Expecially in how page allocation are made in SQL 2008. That is why so many of us still use #Temp instead of declaring a Table Variable.
I am not saying that your statement about how to test this is wrong. It could be.
I am saying that a Table Variable is not a Temp Table and they work differently.
If you want some links to back that up please request in a private message and I will try to get to you tommorow.
My fingers refuse type any more today.

Of course they work differently; temp table changes are rolled back by voluntary rollback, table variable ones are not (both are rolled back by rollback caused by an error); the two things have different creation syntax; temp tables can be explicitly dropped and table variable can't; table variables can be passed as parameters to stored procedures and temp tables can not; and so on. Anyone who thought that two things with so many differences were the same would have to be crazy. However, they both have all data modification operations logged to the transaction log.

Tom

WayneS SSC Guru Points: 95460 More actions · Answer 14

Ignacio A. Salom Rangel (4/7/2011)
Tom.Thomson (4/7/2011)
CirquedeSQLeil (4/7/2011)
I'll put it out there that I missed it because I went with the none option (meaning all are logged based on question phrasing). I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D
Exactly what I did. So it seems a good question to me, as I learnt something new.
I totally agree with Tom. This question was excellent! The whole point of the QOTD is to learn something new and with this question I'm sure many of us just did that. I personally like the way SanDroid persist until he got the answer he was looking for(a bit to eager :-P), but we should all be eager to learn/ clarify things we don't fully understand.

IMHO, he didn't get the answer he wanted; he interpreted it the way he wanted to in order to make himself feel better.

Wayne, I will like to thank you for the question, do you have more questions like this? 😀

Don't know if it is like this, but the next one is in one week.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TomThomson SSC Guru Points: 104773 More actions · Answer 15

WayneS (4/7/2011)
Ignacio A. Salom Rangel (4/7/2011)
Tom.Thomson (4/7/2011)
CirquedeSQLeil (4/7/2011)
I'll put it out there that I missed it because I went with the none option (meaning all are logged based on question phrasing). I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D
Exactly what I did. So it seems a good question to me, as I learnt something new.
I totally agree with Tom. This question was excellent! The whole point of the QOTD is to learn something new and with this question I'm sure many of us just did that. I personally like the way SanDroid persist until he got the answer he was looking for(a bit to eager :-P), but we should all be eager to learn/ clarify things we don't fully understand.
IMHO, he didn't get the answer he wanted; he interpreted it the way he wanted to in order to make himself feel better.

I suspect that you are right.

Also, I didn't like his manner of persistence at all, especially the rather offensive tone and content of some of his messages, for example

SanDroid (4/7/2011)
All I'm saying now is I don't see an link to doumentation in your post, so you must just be posting to flame.
Please do so off topic in a private message.

addressed to Stefan.

Persistence is fine, rudeness is not.

Wayne, I will like to thank you for the question, do you have more questions like this? 😀
Don't know if it is like this, but the next one is in one week.

I'm looking forwards to it.

Tom