Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Truncate rollback


Truncate rollback

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8350 Visits: 11591
Brandie Tarvin (4/23/2010)
[quote]SwaroopRaj (4/23/2010)
Truncates are *minimally logged* (EDIT: in ALL database Recovery models), like having your database in Bulk-Logged Recovery Model. That means there are pointers to the pages of the just removed data that can yank that stuff back if needed.


If I understand all the mexchanisc correct, then the actual TRUNCATE is done by deallocating entire pages. 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. So the pages that were deallocated are not available for reuse until the tran log has been backed up.

Deletes are logged more than Truncates, (EDIT: being fully logged in FULL mode down to the row) even in Bulk-Logged mode, because I think (and I could be wrong here) the pointers are more finite, pointing to the actually extents instead of the pages.


This is not quite correct. A DELETE processes individual rows. Each row deleted gets an entry in the log file, in ALL recovery models (even simple - otherwise, SQL Server would be unable to rollback or to recover after a crash). And all those entries are also written to the log backup.

So, yes, a Truncate can absolutely be rolled back. In fact, I'd be hard pressed to say what data change (not schema change) couldn't be rolled back at all.

One that has already been committed?Whistling
Seriously, I agree. In fact, even most schema changes can be rolled back.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Wow 72 posts for this "little" question. I can offically say this the best QOTD I ever posted w00tHehe.
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2819 Visits: 4152
Steve Jones - Editor (4/22/2010)
I think this was worded a little poorly, and I didn't catch the insert issue. I ran the code on 2008, it worked, I let the question go. I thought the rollback/truncate was tricky enough to be worth 2 points.

I have added 2008 to the question header, as well as noted in the answer for error, "error on the last SELECT".

The debate is interesting here, but for those of you that say that the question isn't fair because it's 2008 specific, 2008 isn't even the current version today. SQL Server 2008 R2 is. I would think that after a year and a half, that you would expect that 2008 is the subject of most questions.

SQL 2000 is EOL, SQL 2005 is getting close to a complete end of support (it's 2010), regardless of what's in *your* environment, consider 2008 to be the standard.


That's fair. How about stating that all questions must be based on SQL 2008?
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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):
- Use a DB in a test environment where you can mess with the data and backups, and let's assume the DB is set to is fully logged.
- Fill a table with a couple GBs of data (using your favorite data-generation method)
- Checkpoint, just to be safe
- Backup the transaction log (ignore this backup file - if you like you can use WITH NO_LOG/TRUNCATE_ONLY, we don't need log chain continuity)
- Shrink the transaction log
-> the transaction log is down to a few MB in size
- Truncate the large table with all that test data.
- Checkpoint, just to be safe
- Back up the transaction log
-> take a look at the size of the transaction log backup... a few MB in size?

I must admit I have not followed these explicit steps in preparation for this post, but does anyone expect behaviour different from this? (does anyone expect the transaction log backup to contain copies of the deallocated pages??)

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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
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):
- Use a DB in a test environment where you can mess with the data and backups, and let's assume the DB is set to is fully logged.
- Fill a table with a couple GBs of data (using your favorite data-generation method)
- Checkpoint, just to be safe
- Backup the transaction log (ignore this backup file - if you like you can use WITH NO_LOG/TRUNCATE_ONLY, we don't need log chain continuity)
- Shrink the transaction log
-> the transaction log is down to a few MB in size
- Truncate the large table with all that test data.
- Checkpoint, just to be safe
- Back up the transaction log
-> take a look at the size of the transaction log backup... a few MB in size?

I must admit I have not followed these explicit steps in preparation for this post, but does anyone expect behaviour different from this? (does anyone expect the transaction log backup to contain copies of the deallocated pages??)



That's an interesting theory, care to script it out and prove it?
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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 Smile
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.
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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 :-P



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
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 :-P



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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
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 Smile
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?
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