<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Wayne Sheffield
»
Logged Operations
90 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Logged Operations
Rate Topic
Display Mode
Topic Options
Author
Message
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Posted Thursday, April 07, 2011 7:50 AM
Hall of Fame
Group: General Forum Members
Last Login: Friday, May 10, 2013 2:49 PM
Points: 3,051,
Visits: 1,356
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
SanDroid
SanDroid
Posted Thursday, April 07, 2011 8:08 AM
Ten 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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
Posted Thursday, April 07, 2011 8:24 AM
Hall of Fame
Group: General Forum Members
Last Login: Friday, May 10, 2013 2:49 PM
Points: 3,051,
Visits: 1,356
SanDroid check this link
http://technet.microsoft.com/en-us/magazine/gg552991.aspx
My blog
Post #1089936
SanDroid
SanDroid
Posted Thursday, April 07, 2011 8:38 AM
Ten 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
SanDroid
SanDroid
Posted Thursday, April 07, 2011 8:46 AM
Ten 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
Hugo Kornelis
Hugo Kornelis
Posted Thursday, April 07, 2011 8:49 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 5,233,
Visits: 7,031
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
UMG Developer
UMG Developer
Posted Thursday, April 07, 2011 9:59 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
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
kevin.l.williams
kevin.l.williams
Posted Thursday, April 07, 2011 10:00 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 2,795,
Visits: 1,124
Haha got it wrong because I didn't know what the version store is. Nice question. Thanks!
Post #1090027
WayneS
WayneS
Posted Thursday, April 07, 2011 10:00 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,367,
Visits: 8,227
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
WayneS
WayneS
Posted Thursday, April 07, 2011 10:01 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,367,
Visits: 8,227
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 »
90 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
