Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Logged Operations Expand / Collapse
Author
Message
Posted Thursday, April 7, 2011 1:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:56 PM
Points: 21,620, Visits: 15,280
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1090210
Posted Thursday, April 7, 2011 1:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:44 PM
Points: 2,762, Visits: 7,235
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
Post #1090214
Posted Thursday, April 7, 2011 1:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1090217
Posted Thursday, April 7, 2011 1:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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?
Post #1090218
Posted Thursday, April 7, 2011 2:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
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
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
Post #1090229
Posted Thursday, April 7, 2011 2:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1090234
Posted Thursday, April 7, 2011 2:28 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1090235
Posted Thursday, April 7, 2011 2:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:56 PM
Points: 21,620, Visits: 15,280
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1090253
Posted Thursday, April 7, 2011 3:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
Excellent!




My blog
Post #1090258
Posted Thursday, April 7, 2011 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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?
Obviously send me the data in a private message.
Post #1090264
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse