Logged Operations
-
Comments posted to this topic are about the item Logged Operations
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 -
Thanks for the question, that is interesting to know. I have always heard everything in SQL Server is logged, but I was thinking that the version store didn't need to be, but I selected none of the above anyway.. Oh well, at least I learned something...
-
Good to know.
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 -
This was removed by the editor as SPAM
-
Good question. Got me two days in a row with lost points.
-
It is very tough to get this question right:)
M&M
-
Very good question!
-
Good question.
-----------------
Gobikannan -
Good question Wayne! Need more coffee after missing this one. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This post was edited by the original Author becuase it's original content is no longer valid.
:Whistling:
-
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
-
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.
-
SanDroid check this link http://technet.microsoft.com/en-us/magazine/gg552991.aspx
-
Ignacio A. Salom Rangel (4/7/2011)
SanDroid check this link http://technet.microsoft.com/en-us/magazine/gg552991.aspxIgnacio,
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 [/i]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.
-
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.
Viewing 15 posts - 1 through 15 (of 89 total)
You must be logged in to reply to this topic. Login to reply