April 7, 2011 at 11:06 am
CirquedeSQLeil (4/7/2011)
D'oh. See I'm still learning. Can we call it moderately but not heavily logged? 😀
You can call it anything you like. :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2011 at 11:59 am
SanDroid (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.:-DSame thing I did. I know that operations affecting the version store are not logged. I am certain that any data affecting the version store is logged. Two very important things to remember if you are trying to save your company from an Oracle Wolf. :w00t: 😎
San, all I can say is the question was perfectly clear to me, and your rebuttal of it isn't.
It sounds like you're stating that the data in the version store is logged, because there's logging of the source of that data. That's an inaccurate conflation based on a logic flaw. If I set up an audit on TableA, and the audit trail for TableA is stored in TableZ, that doesn't mean TableZ is being audited. Yet that's what you're apparently claiming here, and it doesn't make sense.
Diagram it out on a piece of paper. Just a simple flow-chart. You'll quickly see that what's being logged is NOT the data in the version store, it's the source of the data in the version store. Any auditor who understands his job would understand that. It's the old, "who guards the guards" question, and it's been answered in the laws of logic for a couple of thousand years now.
You say you're making this point to add clarity to this, but you're actually accomplishing the opposite. So, please either clarify the point you are actually trying to make, if I've misunderstood you, or clarify the question and answer for yourself (a flow chart will help with that, as suggested).
- 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
April 7, 2011 at 12:16 pm
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?
April 7, 2011 at 12:40 pm
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?
If operations in the version store are not logged, how can data in the version store ever be? Adding data to the version store is an operation in the version store - if that's not logged, than what else will cause the data in the version store to be in the log?
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?
April 7, 2011 at 12:46 pm
Hugo Kornelis (4/7/2011)
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?
If operations in the version store are not logged, how can data in the version store ever be? Adding data to the version store is an operation in the version store - if that's not logged, than what else will cause the data in the version store to be in the log?
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?
I too would be interested in that. I unfortunately don't have the time right now to delve into it.
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
April 7, 2011 at 12:56 pm
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.
April 7, 2011 at 1:02 pm
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... ?
I believe the point here might be that you should provide some evidence other than "I watch this happen all day long" so that others can verify the action and learn something. Clarification in this kind of a discussion happens with evidence, others have posted links to articles. As you're questioning the statements in the article, you should come up with some replicable evidence to demonstrate your point.
--------------------------------------
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
April 7, 2011 at 1:17 pm
Stefan Krzywicki (4/7/2011)
Clarification in this kind of a discussion happens with evidence, others have posted links to articles. As you're questioning the statements in the article, you should come up with some replicable evidence to demonstrate your point.
Stefan, I am not debating anything posted in any article provided.
I am asking for an article that supports a statement in the QOTD.
I am just asking for some good clarification of terms in published documentation so I can learn something that is true.
BIG differance.
As far as triggers rolling back on an error:
Providing a good example would take awhile and IMHO would be, like this post, off topic.
However if you would really like an example personally please send a private message request.
It is nice to see everyone defend something that 90% of the readers got wrong.
However, if you do not have a link to some documentation that says "data affecting the version store is not logged" instead of "operations against the version store are not logged" then please save your time.
Both statements say differant things and I just want to read some documention on the one that matches the answer for our QOTD.
April 7, 2011 at 1:27 pm
SanDroid (4/7/2011)
Stefan Krzywicki (4/7/2011)
Clarification in this kind of a discussion happens with evidence, others have posted links to articles. As you're questioning the statements in the article, you should come up with some replicable evidence to demonstrate your point.
Stefan, I am not debating anything posted in any article provided.
I am asking for an article that supports a statement in the QOTD.
I am just asking for some good clarification of terms in published documentation so I can learn something that is true.
BIG differance.
As far as triggers rolling back on an error:
Providing a good example would take awhile and IMHO would be, like this post, off topic.
However if you would really like an example personally please send a private message request.
It is nice to see everyone defend something that 90% of the readers got wrong.
However, if you do not have a link to some documentation that says "data affecting the version store is not logged" instead of "operations against the version store are not logged" then please save your time.
Both statements say differant things and I just want to read some documention on the one that matches the answer for our QOTD.
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.
--------------------------------------
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
April 7, 2011 at 1:45 pm
Stefan Krzywicki (4/7/2011)
Please forgive my misstatement and allow me to rephraseAs 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"
April 7, 2011 at 1:50 pm
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[/url]
Learn Extended Events
April 7, 2011 at 1:54 pm
SanDroid (4/7/2011)
Stefan Krzywicki (4/7/2011)
Please forgive my misstatement and allow me to rephraseAs 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
April 7, 2011 at 1:57 pm
Stefan Krzywicki (4/7/2011)
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.
April 7, 2011 at 1:58 pm
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?
April 7, 2011 at 2:15 pm
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
Viewing 15 posts - 31 through 45 (of 89 total)
You must be logged in to reply to this topic. Login to reply