Truncate table query

Question

Post reply

Truncate table query

Vimal Lohani

SSCommitted

Points: 1650
More actions
September 10, 2014 at 10:06 pm

#299171

Comments posted to this topic are about the item Truncate table query
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 2:54 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 46 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Please don't say truncate table data isn't logged unless you understand the concepts involved.

😉

The Myth that DROP and TRUNCATE TABLE are Non-Logged

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Koen Verbeeck (9/11/2014)
Please don't say truncate table data isn't logged unless you understand the concepts involved.
😉
The Myth that DROP and TRUNCATE TABLE are Non-Logged

+1, i remember in my earlier days when i busted this myth, the looks on my colleges faces were priceless 😛

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Although I answered more based on the instinct that the data is safely recovered after a rollback. I am still wandering why have I thought that TRUNCATE would drop the content and that would not recover the content after rollback. I wander if there is a difference between 2005 and 2008+ ?

Nice question , thanks for it.

Cheers,

Iulian

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Koen Verbeeck (9/11/2014)
Please don't say truncate table data isn't logged unless you understand the concepts involved.
😉
The Myth that DROP and TRUNCATE TABLE are Non-Logged

+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.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Iulian -207023 (9/11/2014)
Although I answered more based on the instinct that the data is safely recovered after a rollback. I am still wandering why have I thought that TRUNCATE would drop the content and that would not recover the content after rollback. I wander if there is a difference between 2005 and 2008+ ?

No, no difference. Truncate writes page deallocation information to the log file; if the transaction is rolled back, the page deallocations are rolled back and the data is available. If it is committed, the page deallocations are made permanent.

edit: I guess I should mention that this has been true ever since the truncate statement has existed.

Tom

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

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.
😉
The Myth that DROP and TRUNCATE TABLE are Non-Logged
+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.

It's a pity, because how many people read only the explanation and not the reference? Let alone that they read this thread as well.

This will keep the myth alive...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 7

Iulian -207023

SSCertifiable

Points: 7639

September 11, 2014 at 2:43 am

#1744660

I see, thank you Tom

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 8

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.

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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.

It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.

Tom

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 11

Sorry for that, but i can't correct this sentence as i have no admin right for question of the day.

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 12

TomThomson (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.
It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.

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.

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 13

Iulian -207023 (9/11/2014)
TomThomson (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.
It is a DDL statement, but off hand I can't think of any DDL statement that is unlogged. So yes, it creates serious confusion and will tend to permit the myth that TRUNCATE is not transactional.
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.

See Iulian, you know data is saved in pages. when you use truncate, reference the page or pages removed from table and logged in transaction log. that space or page will active till the transaction commit or rollback.

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