TRUNCATE TABLE and ROLLBACK TRAN

Question

Post reply

TRUNCATE TABLE and ROLLBACK TRAN

Viewing 15 posts - 31 through 45 (of 56 total)

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

Great question to start a week with. Thanks, Craig!

Ken SSCoach Points: 17191 More actions · Answer 2

Ken

SSCoach

Points: 17191

February 6, 2012 at 9:29 am

#1443260

Nice question. Thanks Craig.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

Nah - go for it.

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

Toreador SSChampion Points: 11377 More actions · Answer 4

Stewart "Arturius" Campbell (2/6/2012)
Wonder when this was changed?
Granted I haven't worked in Oracle itself since 7b...

It was always the case in Oracle 7 and before as well (ie when I last worked with it, around 1996). Which is why I once thought that truncate couldn't be rolled back in MSSQL either.

Brigadur SSCrazy Points: 2097 More actions · Answer 5

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

I wouldn't mind :). Still, an msdn reference, if there is any relevant, would be a good addition.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

vk-kirov (2/6/2012)
Hugo Kornelis (2/6/2012)
And 34% of them are for "you can never rollback a truncate table statement"
Maybe they are Oracle refugees? :hehe: http://www.sqlservercentral.com/articles/Raw+Materials/71108/

:w00t: AHHH! I've been discovered! Oh well, I've been at least partially in the SQL Server world since 2000 so I guess I should know better by now.

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 7

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

February 6, 2012 at 10:55 am

#1443333

thanks for the question

DBA_Dom SSCrazy Points: 2902 More actions · Answer 8

DBA_Dom

SSCrazy

Points: 2902

February 6, 2012 at 11:06 am

#1443341

Nice and simple. Thanks.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

My sincere apologies to those of you who have done counter-myth articles of this nature. I meant to say no OFFICIAL documentation on recovery mode associations, but there are blogs out there that discuss Truncate Rollbacks. Apparently I faux pas'd when I gave it to Steve. Apologies to y'alls fine work.

Thanks for all the well-wishes. And yes, the distribution is disturbing.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Paul White SSC Guru Points: 150468 More actions · Answer 10

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

I hope not, since I have done this a couple of times. That said, I found two other (non-official, but well-regarded) references and listed them before my own.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 11

Thanks to Craig for the question and Paul for the link to Kalen's article.

It's really good to know that SQL Server will exclusively lock the pages that were truncated until the transaction has ended, so no inconsistent reallocations will occur.

Also about Oracle: why would they create a restriction for the TRUNCATE TABLE in later versions when in version 7 the command could be rolled back?

An excellent QoTD.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

David Burrows SSC Guru Points: 65144 More actions · Answer 12

GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

Or maybe create MotD (Myth of the Day)

Far away is close at hand in the images of elsewhere.
Anon.

Revenant SSC-Forever Points: 42467 More actions · Answer 13

David Burrows (2/8/2012)
GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.
Or maybe create MotD (Myth of the Day)

They seem to pop up on their own, without anyone really trying.

TomThomson SSC Guru Points: 104773 More actions · Answer 14

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

depends who does it. It won't be bad form if you do it.

Tom

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 15

Good question. I missed it but learned something.

http://brittcluff.blogspot.com/

TRUNCATE TABLE and ROLLBACK TRAN

Cookies on SQLServerCentral