Logged Operations

Question

Logged Operations

Viewing 15 posts - 16 through 30 (of 89 total)

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

Good question, Wayne!

I especially liked the link to Gail's blog. I figured that the table variable option was the correct answer and answered without contemplating the version store option. I never before realized that logging of table variable modifications is still needed to support implicit rollback on data error.

Thanks!

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

UMG Developer SSChampion Points: 13482 More actions · Answer 2

SanDroid (4/7/2011)
Perhapse you can explain the question and provide refference material for your only correct answer?

Did you read the information that he linked to about the version store? From: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/01/managing-tempdb-in-sql-server-tempdb-basics-version-store-growth-and-removing-stale-row-versions.aspx

One important point that I did not mention earlier is the “insert” into the Append-Only store are not logged. Why? Well if SQL Server has to fail, all active transactions will be rolled back anyways so the SQL Server does not need the “state” of version store as of the time when SQL Server stopped.

I found another post by a Microsoft employee stating that the version store data is the only thing not logged, but I can't find it again right now.

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 3

Haha got it wrong because I didn't know what the version store is. Nice question. Thanks!

WayneS SSC Guru Points: 95409 More actions · Answer 4

SanDroid (4/7/2011)
You have references to validate the incorrect answers, but not to the correct one!?!?! :sick:
Looks like 90% if the people that tried to answer this question did not understand it either. :crazy:
I learned nothing from this question.
We use row versioning and Full Recovery Model on our SQL 2005 server and have enabled a level of auditing so the changes to our version comuns are logged. Do you mean with defaults enabled? Is there a certain area of this system that is not logged that would have benifit?
Perhapse you can explain the question and provide refference material for your only correct answer?
:Whistling:

I did post a link to a blog by the Microsoft SQL Server Storage Engine team in which they state that the version store is not logged. And as Ignacio A. Salom Rangel points out, it is also covered in Paul Randal's (who was on the SQL Storage Engine team for 9 years) blog on the TRUNCATE TABLE being logged (but somehow, I never saw that before - thanks for pointing that out.)

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

WayneS SSC Guru Points: 95409 More actions · Answer 5

mohammed moinudheen (4/7/2011)
It is very tough to get this question right:)

Actually, I meant it to be tough. The other choices are all myths that a lot of people believe, and I wanted to debunk those at the same time.

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

UMG Developer SSChampion Points: 13482 More actions · Answer 6

Here is another link to Technet that contains this information: http://technet.microsoft.com/en-us/magazine/gg552991.aspx

Again, something written by Paul Randal.

WayneS SSC Guru Points: 95409 More actions · Answer 7

Hugo Kornelis (4/7/2011)
Good question, Wayne!
I especially liked the link to Gail's blog. I figured that the table variable option was the correct answer and answered without contemplating the version store option. I never before realized that logging of table variable modifications is still needed to support implicit rollback on data error.
Thanks!

Thanks Hugo. That means a lot to me, coming from you.

(I figured that I would need to prove the table variable logging)

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

Bill Sheets Valued Member Points: 62 More actions · Answer 8

Are inserts into a table variable really logged? It doesn't look like it to me. I guess you can say that since they are persisted in tempdb, they must be, but you cannot rollback an insert into a table variable. Running the code below will return 1 row. If it was logged, it seems like it would not return a row.

declare @t table(

c1int null

);

begin tran;

insert into @t values(1);

rollback tran;

select * from @t;

SanDroid SSChampion Points: 10068 More actions · Answer 9

WayneS (4/7/2011)
I did post a link to a blog by the Microsoft SQL Server Storage Engine team in which they state that the version store is not logged. And as Ignacio A. Salom Rangel points out, it is also covered in Paul Randal's (who was on the SQL Storage Engine team for 9 years) blog on the TRUNCATE TABLE being logged (but somehow, I never saw that before - thanks for pointing that out.)

Wayne,

These talk about operations against the version store not being logged. Not the data.

Your answer was that "Data affecting the version store" was the thing not logged. I see your correct answer as incorrect as the other "myths" in your question.

Version store operations = Not logged, could not be rolled back if they were.

Data affecting Version store (like updates/inserts by triggers ) = Logged and are rolled back when errors occur.

q: Are operations to data (MARS, updates/inserts by triggers) in the version store logged?

a: Only as much as the transaction in the DB that initiated them.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Bill Sheets (4/7/2011)
Are inserts into a table variable really logged?

Yup, absolutely, as proven here: http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

It doesn't look like it to me. I guess you can say that since they are persisted in tempdb, they must be, but you cannot rollback an insert into a table variable. Running the code below will return 1 row. If it was logged, it seems like it would not return a row.

The fact that a modification cannot be explicitly rolled back in no way implies that it is an unlogged operation. By design table variables ignore transactions. It's still a logged operation because it can still be rolled back by an error, as I prove in that blog post (which Wayne linked from the answers)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

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

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

SanDroid SSChampion Points: 10068 More actions · Answer 12

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

Same 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: 😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

CirquedeSQLeil (4/7/2011)
I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D

Table variables aren't minimally logged. They are fully logged. Minimally logged means that only the page allocation/deallocation are logged. Table variables have the row modifications logged.

Ok, it's not as much logging as a table in a user database, but that's because TempDB needs less info, but it's not minimally logged.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 14

Nice question, I learned something and got some new reading to do!

--------------------------------------
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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

GilaMonster (4/7/2011)
CirquedeSQLeil (4/7/2011)
I knew table variables and truncates were minimally logged and thought version store was too. I learned something new.:-D
Table variables aren't minimally logged. They are fully logged. Minimally logged means that only the page allocation/deallocation are logged. Table variables have the row modifications logged.
Ok, it's not as much logging as a table in a user database, but that's because TempDB needs less info, but it's not minimally logged.

D'oh. See I'm still learning. Can we call it moderately but not heavily logged? 😀

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