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 ««12345»»»

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


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
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
Post #1089900
Posted Thursday, April 7, 2011 8:08 AM
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
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.
Post #1089919
Posted Thursday, April 7, 2011 8:24 AM


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
SanDroid check this link http://technet.microsoft.com/en-us/magazine/gg552991.aspx




My blog
Post #1089936
Posted Thursday, April 7, 2011 8:38 AM
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
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.



Post #1089947
Posted Thursday, April 7, 2011 8:46 AM
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
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.
Post #1089956
Posted Thursday, April 7, 2011 8:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:22 PM
Points: 5,975, Visits: 8,233
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
Post #1089960
Posted Thursday, April 7, 2011 9:59 AM
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)
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.
Post #1090023
Posted Thursday, April 7, 2011 10:00 AM


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: 2 days ago @ 12:20 PM
Points: 3,139, Visits: 1,258
Haha got it wrong because I didn't know what the version store is. Nice question. Thanks!


Post #1090027
Posted Thursday, April 7, 2011 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 PM
Points: 6,594, Visits: 8,877
SanDroid (4/7/2011)
You have references to validate the incorrect answers, but not to the correct one!?!?!

Looks like 90% if the people that tried to answer this question did not understand it either.

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?



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
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 #1090029
Posted Thursday, April 7, 2011 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 PM
Points: 6,594, Visits: 8,877
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
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 #1090032
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse