SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logged Operations


Logged Operations

Author
Message
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32296 Visits: 18552
In an effort to clarify - a request has been sent to Paul Randal on the subject.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Sioban Krzywicki
Sioban Krzywicki
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1827 Visits: 8090
SanDroid (4/7/2011)
Stefan Krzywicki (4/7/2011)

Please forgive my misstatement and allow me to rephrase
As you're questioning the statements in the QOTD and others have posted links to articles supporting the conclusion you should post something supporting what you're stating otherwise no-one has any reason to accept your statements.

Stefan - Nobody has responded with an article that states exactly what was todays answer. That includes both of your posts. That includes the hour I spent looking for one before my first post.

I have understood since before today that "operations against the version store" are not logged.

Today I want to understand and learn about how "data affecting the version store" is not logged.

It is a very thought provoking question Wayne has created and I like it very much becuase it puts to bed a lot of SQL Logging "Myth's".
My two favorites having always been "Truncat Table is not logged" and "Temp Tables are not logged"


So to clarify your position, because you don't think anyone else has shown evidence that convinces you the QOTD is correct, you don't feel the need to show evidence for what you "see every day". Even though if you see it every day it must be pretty easy to obtain such evidence or tell other people how they can see it?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 1046
Stefan Krzywicki (4/7/2011)
[quote]So to clarify your position, because you don't think anyone else has shown evidence that convinces you the QOTD is correct, you don't feel the need to show evidence for what you "see every day". Even though if you see it every day it must be pretty easy to obtain such evidence or tell other people how they can see it?

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.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2684 Visits: 2204
SanDroid (4/7/2011)
Today I want to understand and learn about how "data affecting the version store" is not logged.


I assume what you are trying to get at is that the "data affecting the version store" is the data changed in the source table that causes data to be inserted into the version store, not the actual data inserted into the version store. And I can see how you could interpret that statement to mean that.

The data added to the version store is never logged in tempdb, but the source of it is logged in the source database. (Just like it would be if row versioning wasn't being used.)

Is that what you are trying to point out?
WayneS
WayneS
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9835 Visits: 10569
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.

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 9730
SanDroid (4/7/2011)
GSquared (4/7/2011)
[quote]
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
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23447 Visits: 9730
SanDroid (4/7/2011)
Hugo Kornelis (4/7/2011)
[quote]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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32296 Visits: 18552
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

Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3946 Visits: 1439
Excellent!




My blog

SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 1046
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search