SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Truncate rollback


Truncate rollback

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18279 Visits: 12426
Paul White NZ (4/24/2010)
I believe Hugo is confusing the logging behaviour of TRUNCATE TABLE with the behaviour of minimally-logged data changes under the BULK_LOGGED recovery model.

The allocation unit deallocations performed by TRUNCATE TABLE (whether or not these are deferred and performed asynchronously on a background thread) do not change data - so BCM bits are not set, and the affected pages are not included in the next log backup.

All that needs to be logged for full recoverability is the fact that the allocation units were deallocated. See Tracking Modified Extents for details of how SQL Server uses the Bulk Changed Map, and the impact on transaction log backups.


Yes, that was indeed what I was confused with. Thanks, Paul, for correcting me, and for providing the link to help me refresh my memory. All the bits and pieces were there, they just failed to connect in the right way :-D

And Tao, thank you for posting the script to demonstrate that I was wrong. Boy, if only I had taken the time to test this myself before posting... Ah well, water under the bridge.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 510
Well, here's a new one: I got it wrong - answered 0 - because of the way safary on iPhone renders the code. I was Like WTF way down 4 pages untill someone quoted the question and I saw THE actual last select AFTER the rollback statement Sad
guess i'll have to use my 2fingers more often to check if there IS more code in the blue box. Or maybe ssc will get an iPhone friendly theme?
Am I alone in this?

Hrvoje

Hrvoje Piasevoli
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59353 Visits: 13297
That will teach you using Safari :-P


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
hrvoje.piasevoli
hrvoje.piasevoli
Mr or Mrs. 500
Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)Mr or Mrs. 500 (541 reputation)

Group: General Forum Members
Points: 541 Visits: 510
da-zero (4/28/2010)
That will teach you using Safari :-P

nice one da-zero Smile let's open a new item at connect: IE for iPhone:-D

I am actually glad I messed up for the sake of some valuable info given in this discussion.
And, somewhere in there a good question was raised: is @@version enough or should you query db compatibility level in order to use some new feature?

--
great people here @ ssc

Hrvoje

Hrvoje Piasevoli
saagrawal
saagrawal
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 6
ca anybody give me the more info about this quetion.
because i am really surprised that how to rollback truncate command.

i have tried the same on 2000 and 2005, it is working opposite to my knowledge.

i have always read that, we cant rollback truncate statement, but it is happening with the given example.

Please revert
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35907 Visits: 9261
saagrawal (7/30/2010)
it is working opposite to my knowledge.


Where did you read that you can't rollback a truncate? Do you have specific sources?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
saagrawal
saagrawal
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 6
Thanks...
i was wrong.

can you please provide the some sources for the Sql servers Questions and Answers so that i can grow my self.

Thanks in Advance

Regards,
Saurabh A
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35907 Visits: 9261
The Q&A come from different people and those people use different references. Usually each question has the source listed below it once you've tried to answer the question.

But if you want to start without those references, start with Books Online, then start googling different terms that you hear on the forums. You'll come up with all sorts of threads discussing different things.

And if you still can't find the answers you're seeking, post to SCC.com. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search