April 23, 2010 at 7:42 pm
Ninja's_RGR'us (4/23/2010)
That's an interesting theory, care to script it out and prove it?
I'm not sure my theory is that interesting, I suspect that I just misunderstood what Hugo was saying - but here's a script illustrating my point, anyway:
--Tiny DB created in the default folder, default collation, etc, set to 10% autogrow - terrible
-- for performance & fragmentation, but we're doing this for testing only. Don't try this at home kids!
-- (also, don't create databases in the root of your system drive, or even allow the SQL Service account access to it!)
CREATE DATABASE SimpleTestDB
ON PRIMARY (NAME = SimpleTestDB_Data, FILENAME = 'C:\SimpleTestDB_Data.mdb', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON (NAME = SimpleTestDB_Log, FILENAME = 'C:\SimpleTestDB_Data.ldb', SIZE = 1, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
--Not sure what the default is, let's set it anyway.
ALTER DATABASE SimpleTestDB
SET RECOVERY FULL
GO
USE SimpleTestDB
GO
--Confirm the file sizes (in Pages):
SELECT name, filename, size FROM sysfiles
GO
--Quickly generate dummy data, let's use existing structures to accumulate data relatively fast
-- (Might as well use a heap, we will never query; again, not at home kids!)
-- (Took about 4 minutes to create 300 MB of data on a pretty-low-spec test server)
SET NOCOUNT ON
SELECT * INTO JunkData FROM master.dbo.sysobjects
DECLARE @DataInsertIterationCounter Int
SET @DataInsertIterationCounter = 0
WHILE @DataInsertIterationCounter < 1000
BEGIN
INSERT INTO JunkData SELECT * FROM master.dbo.sysobjects
SET @DataInsertIterationCounter = @DataInsertIterationCounter + 1
END
SET NOCOUNT OFF
GO
--Confirm the new file sizes
SELECT name, filename, size FROM sysfiles
GO
--Truncate the transaction log - (kids, you know the drill)
CHECKPOINT
BACKUP LOG SimpleTestDB WITH TRUNCATE_ONLY
GO
--Shrink the logfile so that we can see the effect of truncating the table
DBCC SHRINKFILE (SimpleTestDB_Log)
GO
--Confirm the logfile is back to being tiny:
SELECT name, filename, size FROM sysfiles
GO
--Back up the DB so that we actually can do a transaction log backup later:
BACKUP DATABASE SimpleTestDB TO DISK = 'C:\SimpleTestDB_Pre-Truncate_Full_Backup_(Junk).BAK'
GO
--Truncate the table - this is the cool bit - takes only a sec to "delete" (deallocate?) all that data!
TRUNCATE TABLE JunkData
GO
--Confirm the logfile still tiny despite the table truncation:
SELECT name, filename, size FROM sysfiles
GO
--Actually back up the transaction log
CHECKPOINT
BACKUP LOG SimpleTestDB
TO DISK = 'C:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
GO
--Check the size of the transaction log backup file
--SQL 2000 or earlier
exec xp_getfiledetails 'C:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
--OR if your server allows xp_cmdshell
exec master..xp_cmdshell 'dir c:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
--OR otherwise - go look up the size of the file :)
GO
--Clean Up
DROP DATABASE SimpleTestDB
--Remember to delete the 300-MB DB backup file and the stray transaction log backup file too! (manually, sorry, I'm not going to rely on the presence of xp_cmdshell)
The transaction log backup file is tiny - the fact that the pages have been deallocated is presumably logged (a list of page references?), but the pages themselves are not backed up to the transaction log (or transaction log backup) file.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 24, 2010 at 1:55 am
Always good to see this question - the myth that TRUNCATE TABLE is non-logged (and so cannot be rolled back) is a persistent one.
Quite clever using 2008-only syntax too - which largely defeats the 'run it then answer' crowd.
Complaining that the INSERT syntax is invalid sounds like sour grapes to me 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 24, 2010 at 2:36 am
Tao Klerks (4/23/2010)
Hugo Kornelis (4/23/2010)
The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page.I'm not sure whether I'm misunderstanding you, but to my mind this makes no sense (and does not correspond to my practical experience)
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.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 24, 2010 at 4:52 am
Paul White NZ (4/24/2010)
Always good to see this question - the myth that TRUNCATE TABLE is non-logged (and so cannot be rolled back) is a persistent one.Quite clever using 2008-only syntax too - which largely defeats the 'run it then answer' crowd.
Complaining that the INSERT syntax is invalid sounds like sour grapes to me 😛
Ya this made it like a 1-2 punch in this one... I feel that most people who complained about it had a chance to learn 2 things (and yes I do see your POV). Now I can't do a darn thing if they didn't learn and just want to whine about it ;-). Anyhow I still feel this question gave the intended results... make peope think & learn and start a nice conversation about the topic.
April 24, 2010 at 4:53 am
Tao Klerks (4/23/2010)
Ninja's_RGR'us (4/23/2010)
That's an interesting theory, care to script it out and prove it?I'm not sure my theory is that interesting, I suspect that I just misunderstood what Hugo was saying - but here's a script illustrating my point, anyway:
--Tiny DB created in the default folder, default collation, etc, set to 10% autogrow - terrible-- for performance & fragmentation, but we're doing this for testing only. Don't try this at home kids!
-- (also, don't create databases in the root of your system drive, or even allow the SQL Service account access to it!)
CREATE DATABASE SimpleTestDB
ON PRIMARY (NAME = SimpleTestDB_Data, FILENAME = 'C:\SimpleTestDB_Data.mdb', SIZE = 2, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON (NAME = SimpleTestDB_Log, FILENAME = 'C:\SimpleTestDB_Data.ldb', SIZE = 1, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
--Not sure what the default is, let's set it anyway.
ALTER DATABASE SimpleTestDB
SET RECOVERY FULL
GO
USE SimpleTestDB
GO
--Confirm the file sizes (in Pages):
SELECT name, filename, size FROM sysfiles
GO
--Quickly generate dummy data, let's use existing structures to accumulate data relatively fast
-- (Might as well use a heap, we will never query; again, not at home kids!)
-- (Took about 4 minutes to create 300 MB of data on a pretty-low-spec test server)
SET NOCOUNT ON
SELECT * INTO JunkData FROM master.dbo.sysobjects
DECLARE @DataInsertIterationCounter Int
SET @DataInsertIterationCounter = 0
WHILE @DataInsertIterationCounter < 1000
BEGIN
INSERT INTO JunkData SELECT * FROM master.dbo.sysobjects
SET @DataInsertIterationCounter = @DataInsertIterationCounter + 1
END
SET NOCOUNT OFF
GO
--Confirm the new file sizes
SELECT name, filename, size FROM sysfiles
GO
--Truncate the transaction log - (kids, you know the drill)
CHECKPOINT
BACKUP LOG SimpleTestDB WITH TRUNCATE_ONLY
GO
--Shrink the logfile so that we can see the effect of truncating the table
DBCC SHRINKFILE (SimpleTestDB_Log)
GO
--Confirm the logfile is back to being tiny:
SELECT name, filename, size FROM sysfiles
GO
--Back up the DB so that we actually can do a transaction log backup later:
BACKUP DATABASE SimpleTestDB TO DISK = 'C:\SimpleTestDB_Pre-Truncate_Full_Backup_(Junk).BAK'
GO
--Truncate the table - this is the cool bit - takes only a sec to "delete" (deallocate?) all that data!
TRUNCATE TABLE JunkData
GO
--Confirm the logfile still tiny despite the table truncation:
SELECT name, filename, size FROM sysfiles
GO
--Actually back up the transaction log
CHECKPOINT
BACKUP LOG SimpleTestDB
TO DISK = 'C:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
GO
--Check the size of the transaction log backup file
--SQL 2000 or earlier
exec xp_getfiledetails 'C:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
--OR if your server allows xp_cmdshell
exec master..xp_cmdshell 'dir c:\SimpleTestDB_Post-Truncate_Log_Backup.TRN'
--OR otherwise - go look up the size of the file :)
GO
--Clean Up
DROP DATABASE SimpleTestDB
--Remember to delete the 300-MB DB backup file and the stray transaction log backup file too! (manually, sorry, I'm not going to rely on the presence of xp_cmdshell)
The transaction log backup file is tiny - the fact that the pages have been deallocated is presumably logged (a list of page references?), but the pages themselves are not backed up to the transaction log (or transaction log backup) file.
I think I'm missing your point. Where are you rolling back the changes after the tlog backup?
April 24, 2010 at 9:05 am
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.
April 27, 2010 at 4:42 pm
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
April 28, 2010 at 12:22 am
That will teach you using Safari 😛
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 28, 2010 at 1:21 am
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:-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
July 30, 2010 at 8:45 am
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
July 30, 2010 at 8:50 am
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?
July 30, 2010 at 9:07 am
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
August 3, 2010 at 7:45 am
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. @=)
Viewing 13 posts - 76 through 87 (of 87 total)
You must be logged in to reply to this topic. Login to reply