September 11, 2014 at 4:57 am
What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.
Most probably there is a mechanism to resolve that but which and how ?
Please ignore if it does not make sense.
I'd guess that the deallocated pages are not available for re-use until the transaction is committed, so that if the transaction rolls back it can be sure that they haven't been modified by another operation.
September 11, 2014 at 5:07 am
j.green (9/11/2014)
What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.
Most probably there is a mechanism to resolve that but which and how ?
Please ignore if it does not make sense.
I'd guess that the deallocated pages are not available for re-use until the transaction is committed, so that if the transaction rolls back it can be sure that they haven't been modified by another operation.
Oh yes , I got it now, thanks a lot both of you.
September 11, 2014 at 5:11 am
Iulian -207023 (9/11/2014)
What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.Most probably there is a mechanism to resolve that but which and how ?
Please ignore if it does not make sense.
The truncating transaction holds an exclusive lock on each allocation unit involved until the transaction is either committed or rolled back. That prevents anyone other transaction from allocating the allocation unit (even NOLOCK hints or READ_UNCOMMITTED isolation level doesn't allow the allocation unit locks to be bypassed) before the truncating transaction is finished.
Tom
September 11, 2014 at 5:46 am
Hehehehe... I was thinking about posting this exact same msg :hehe:
twin.devil (9/11/2014)
Koen Verbeeck (9/11/2014)
Please don't say truncate table data isn't logged unless you understand the concepts involved.😉
+1, i remember in my earlier days when i busted this myth, the looks on my colleges faces were priceless 😛
---------------
Mel. 😎
September 11, 2014 at 5:56 am
Iulian -207023 (9/11/2014)
j.green (9/11/2014)
What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.
Most probably there is a mechanism to resolve that but which and how ?
Please ignore if it does not make sense.
I'd guess that the deallocated pages are not available for re-use until the transaction is committed, so that if the transaction rolls back it can be sure that they haven't been modified by another operation.
Oh yes , I got it now, thanks a lot both of you.
The thing that threw me about the question was the "data can be recovered" part of the answer. I got it right, but this part really made me wonder if I missed something is the relatively easy question because there's no actual recovery being performed here. I chalk it up to language. I got it right, but this is the only thing that made me stop and think of where the trick was.
I'm glad the obvious error in the explanation was already pointed out. I think the myth might have it's origins in older versions of Oracle, where everything is a transaction except DDL and you can continue on until you commit. I don't know if it's still true in the current version, but you could be going through life running insert and update statements one after the other at the SQL*Plus command line, but nothing will be committed until you fire the commit. Unless, that is, you fire a DDL statement. At that point, all previous DML statements are committed. A lot of people believe that TRUNCATE is a DML statement, when is is really a DDL that immediately fires a commit.
September 11, 2014 at 6:08 am
Koen Verbeeck (9/11/2014)
Vimal Lohani (9/11/2014)
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.
All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.
Thanx 4 pointing this out as some poeple here don`t know that. But still the purpose itself of the question is good.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 11, 2014 at 6:10 am
Hany Helmy (9/11/2014)
Koen Verbeeck (9/11/2014)
Vimal Lohani (9/11/2014)
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.this means if page is recovered from the transaction log, data can be recovered.data or row directly not logged on transaction log, because of this it is too fast.
All fine and dandy, but the statement "TRUNCATE is DDL operation and it is not logged in log file." is incorrent and can lead to serious confusion.
Thanx 4 pointing this out as some poeple here don`t know that. But still the purpose itself of the question is good.
Indeed. At least the question debunks the myth that you cannot rollback TRUNCATE TABLE.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 11, 2014 at 6:42 am
TomThomson (9/11/2014)
Iulian -207023 (9/11/2014)
What happens if while the pages are deallocated , another task / process / txn ( or something ) allocates that space for other purposes , and I run the rollback after the pages were alocated by this second task / process / txn.Most probably there is a mechanism to resolve that but which and how ?
Please ignore if it does not make sense.
The truncating transaction holds an exclusive lock on each allocation unit involved until the transaction is either committed or rolled back. That prevents anyone other transaction from allocating the allocation unit (even NOLOCK hints or READ_UNCOMMITTED isolation level doesn't allow the allocation unit locks to be bypassed) before the truncating transaction is finished.
Thank you Tom, I just had a look to the sys.allocation_units on msdn and it looks good , it worst a quick look.
I assume that truncate (1) locks the allocation unit = data_pages for that table and (2) flag the data pages as unnallocated,
then on commit the lock is released (-1) and so the data is removed
while on rollback both the lock and the unnalocated_flag are removed (-1) & (-2) and so the data is "recovered" / actually it was never in danger 🙂
I think I got it , indeed good question
September 11, 2014 at 9:49 am
TomThomson (9/11/2014)
Koen Verbeeck (9/11/2014)
Please don't say truncate table data isn't logged unless you understand the concepts involved.😉
+1000000
It's amazing, when even the reference quoted in the explanation states that the operation is in fact logged that teh explanation can say that it isn't.
It's a useful queston, but the explanation is nonsense.
+10
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
September 11, 2014 at 9:49 am
Explanation corrected. I've removed the part that says the data cannot be recovered.
September 11, 2014 at 10:13 am
Steve Jones - SSC Editor (9/11/2014)
Explanation corrected. I've removed the part that says the data cannot be recovered.
Thanks Steve
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
September 11, 2014 at 12:04 pm
I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.
Aigle de Guerre!
September 12, 2014 at 4:33 am
Meow Now (9/11/2014)
I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.
It's fully logged.
A minimally logged operation is one where the logging behavior changed between the recovery models, hence both their follow up question and reaction to your answer makes sense.
Minimally logged operations are all insert-based (select into, bulk insert, bcp in, etc)
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
September 12, 2014 at 5:28 am
GilaMonster (9/12/2014)
Meow Now (9/11/2014)
I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.It's fully logged.
A minimally logged operation is one where the logging behavior changed between the recovery models, hence both their follow up question and reaction to your answer makes sense.
Minimally logged operations are all insert-based (select into, bulk insert, bcp in, etc)
I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.
It says:
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
what I would be curious is: what triggers are supposed to fire ?
Cheers,
Iulian
September 12, 2014 at 5:33 am
Iulian -207023 (9/12/2014)
GilaMonster (9/12/2014)
Meow Now (9/11/2014)
I was asked this very thing in an interview a while back. Except, they wanted to know if a TRUNCATE was fully or minimally logged. While I mentioned that all of the data could be rolled back with the use of a transaction, there was no way to roll back individual records...so minimally logged was my answer. Their follow up question was whether or not the recovery model would make any difference. I said no, but they just stared at me like I was crazy.It's fully logged.
A minimally logged operation is one where the logging behavior changed between the recovery models, hence both their follow up question and reaction to your answer makes sense.
Minimally logged operations are all insert-based (select into, bulk insert, bcp in, etc)
I found this, on msdn, it must be on a different context and that would explain it or it can be a typo as well, who knows ... not so interesting to me, I understand what minimal logged means now. Thanks for that.
It says:
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables.
what I would be curious is: what triggers are supposed to fire ?
Unfortunately MSDN is not always to be trusted.
Replication doesn't need triggers, so I wonder as well what they meant. Perhaps DDL-triggers?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply