Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logged Operations


Logged Operations

Author
Message
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 1439
The link to Paul Randal's post gives you the gives you the answer. http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281930%29-TRUNCATE-TABLE-is-non-logged.aspx




My blog

SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
Wayne,
From what I can find to read about the version store, only logged and transactional operation Data is ever in it.
So I wonder why you think that data affecting the version store in the tempdb is not logged?
I would agree that the tempdb is not logged, and that the movment of Data in and out of the version store is not transactionaly logged.
I can find no documentation that supports your correct answer as worded in your question.
I can find the documentation bellow that supports how to use the DMV and System Views to monitor the version store.

From BOL article "Capacity planing for TempDb" --
http://msdn.microsoft.com/en-us/library/ms345368.aspx

Row versioning is a general framework that is used to support the following features:
Triggers
Multiple Active Result Sets (MARS)
Index operations that specify the ONLINE option
Row versioning-based transaction isolation levels:
A new implementation of read-committed isolation level
that uses row versioning to provide statement-level read consistency.

A snapshot isolation level to provide transaction-level read consistency.

Row versions are held in the tempdb version store for as long as an active transaction must access it. The content of the current version store is returned in sys.dm_tran_version_store. Version-store pages are tracked at the file level because they are global resources. You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. Version-store cleanup must consider the longest running transaction that requires access to the particular version. The longest running transaction related to version store clean-up can be discovered by viewing the elapsed_time_seconds column in sys.dm_tran_active_snapshot_database_transactions. The counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object can be used to monitor the size and rate of growth of the row-version store in tempdb. For more information, see SQL Server, Transactions Object.
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 1439
SanDroid check this link http://technet.microsoft.com/en-us/magazine/gg552991.aspx




My blog

SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
Ignacio A. Salom Rangel (4/7/2011)
SanDroid check this link http://technet.microsoft.com/en-us/magazine/gg552991.aspx

Ignacio,
Nice link. It helps prove my point.

"The only truly non-logged operations in SQL Server are those affecting the version store in tempdb, which supports features like snapshot isolation and online index operations. These can be non-logged because there’s never a need to roll back a version store operation or run crash-recovery on the tempdb database."

This article talks about logging version store operations.
The question specificly states "Data affecting the version store".
They are two differant things, two differant concepts.
Version store operations, and version store data are completely differant things.

Still waiting to hear from WayneS becuase I would not be supprised if he edited this question from "Data" to "operations", but we are seeing the un-edited version.
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
I want to make sure everyone understands how important it is to know that
Version store operations, and version store data are completely differant things.

The version store supports all MARS and trigger execution in SQL.

If the transaction data changes in the version store executed by trigger operations was not logged then that would leave a huge auditing and logging hole in SQL server for updates to relational data in other tables that are made by triggers.

I can't find anything that would support that, and I am happier for it.
I agree completely that version store OPERATIONS are not logged, but not that the Data affecting the version store is not.
That data will always be logged by the transactions that send it there.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11587
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
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.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3379 Visits: 1323
Haha got it wrong because I didn't know what the version store is. Nice question. Thanks!



WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
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, 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

WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
mohammed moinudheen (4/7/2011)
It is very tough to get this question rightSmile



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

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