Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789

Truncate rollback Expand / Collapse
Author
Message
Posted Saturday, April 24, 2010 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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

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
Post #909972
Posted Tuesday, April 27, 2010 4:42 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, 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
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
Post #911539
Posted Wednesday, April 28, 2010 12:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 13,644, Visits: 10,538
That will teach you using Safari



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #911634
Posted Wednesday, April 28, 2010 1:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, Visits: 510
da-zero (4/28/2010)
That will teach you using Safari

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

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
Post #911665
Posted Friday, July 30, 2010 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 30, 2010 9:05 AM
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
Post #961447
Posted Friday, July 30, 2010 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 7,197, Visits: 6,342
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #961457
Posted Friday, July 30, 2010 9:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 30, 2010 9:05 AM
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
Post #961477
Posted Tuesday, August 3, 2010 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 7,197, Visits: 6,342
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #962810
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse