THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

TSQL Tuesday #11:Rolling Back TRUNCATE TABLE


“It ain't what people don't know that hurts them it's what they know that ain't so” 
-- commonly attributed to Mark Twain

SQL Server has been around for a long time now, and people who learn one version well sometimes have  monumental task when a new version comes out. They need to figure out what changed. The "What's New" documentation usually gives a good idea of the major new features, but sometimes when a more minor feature just change a bit, it's may get overlooked in all the hoopla of learning about the BIG new stuff.

One change that occurred very long ago that many people still haven't hooked into is that fact that a TRUNCATE TABLE operation can be rolled back! Even in my advanced classes, with some of the best DBAs out there, when I start talking about TRUNCATE TABLE, there are frequently one or two people who think it is unlogged and thus can't be rolled back.

Let me state now… THIS IS NOT TRUE.

And in fact, it is very easy to prove that it is not true… but if you don't suspect that it is not true, why would you even test it?

TRUNCATE TABLE is logged, but SQL Server doesn't log every single row as it TRUNCATEs the table, so you might think that this command falls into the category of commands we call 'minimally logged'. But although indeed minimal information is logged for this operation, it is not technically in that category. The official 'minimally logged' operations also cause a bit to be set in a big bit map, so that when you are running in BULK_LOGGED recovery model and make a log backup, SQL Server can capture all the data involved in 'minimally logged' operations into the transaction log. This allows the data to be recovered when restoring from backup.

But for TRUNCATE TABLE, SQL Server only logs the fact that the operation happened, along with information about the pages and extents that were deallocated. So that's minimal information. And by recording which pages and extents were deallocated, there's enough information to roll back, by just REallocating those pages. A log  backup only needs the information that the TRUNCATE TABLE occurred. And to restore the TRUNCATE TABLE, the operation is just reapplied. The data involved is not needed during RESTORE (like it would be for a true 'minimally logged' operation like a BULK INSERT).

So what if the transaction in which the TRUNCATE TABLE occurs stay open for a long time? How does SQL Server know not to reuse the pages that belonged to the table? It turns out the pages and/or extents involved are locked with an eXclusive lock, and just like all X locks, they are held until the end of the transaction.  And as long as the pages or extents are locked, they can't be deallocated, and certainly cannot be reused.

So here's some code to see for yourself. I'll make a copy of a table from the AdventureWorks database:

USE AdventureWorks

                    WHERE name = 'Products'
                    AND schema_id = 1)
    DROP TABLE dbo.Products;

SELECT * INTO dbo.Products
FROM Production.Product;

Now we'll look at the count of rows, and the pages that belong to the table.

SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);

We get a count of 504 rows and the following pages:

File    Page
-----   -------
1       23027
1       23029
1       23030
1       26992
1       26993
1       33352
1       33353
1       33354
1       33355
1       33356
1       33357
1       42486
1       42487
1       42488
1       42489

Next, start a transaction and truncate the table. Verify that DBCC IND shows no pages belonging to the table and the count is 0. Then, look at the locks.

TRUNCATE TABLE dbo.Products;
SELECT COUNT(*) FROM dbo.Products;

DBCC IND('AdventureWorks', 'Products', -1);
DBCC EXTENTINFO('AdventureWorks', 'Products', -1);

SELECT resource_type, resource_description,
        request_mode FROM sys.dm_tran_locks
WHERE  resource_type IN ('EXTENT', 'PAGE')
AND   resource_database_id = DB_ID('AdventureWorks');

You should see no rows from DBCC IND, and 0 rows from count(*). But the locks info should return something like:

resource_type resource_description request_mode
------------- -------------------- ------------
EXTENT        1:33352              X
PAGE          1:42486              X
EXTENT        1:42488              X
PAGE          1:42487              X
PAGE          1:42488              X
PAGE          1:42489              X
PAGE          1:23027              X
PAGE          1:23030              X
PAGE          1:23029              X
PAGE          1:26992              X
PAGE          1:26993              X

So the extent and page locks include all the pages that we saw in the DBCC IND output. (Remember, an extent is 8 contiguous pages, starting at the one that describes the extent.)

Only after you ROLLBACK the transaction will the locks be released, and you should see all the rows and pages back in the table again.

SELECT COUNT(*) FROM dbo.Products;
DBCC IND('AdventureWorks', 'Products', -1);

So now you know!


Published Tuesday, October 12, 2010 2:02 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

A very wise and sagacious post as usual. I find the same thing (the misconsception amongst active DBAs) with TRUNCATE or minimally logged transactions altogether. Hey, that would make a great 2-3 hour talk. That or hobts (Sorry Kalen, couldn't resist - don't blacklist me!).

October 12, 2010 5:39 PM

mbourgon said:

Ha! Awesome.  I've actually used that before (doing a truncate within a transaction), stunned that it worked that way.  Thanks for the reminder.

October 14, 2010 1:22 PM

arjanf said:

Hi Kalen,

I did deliver some comment on a linked in discussion a while ago based on the same subject.

I believe that when you do a truncation on a table with an Identity(1,1) column in it the identity is kept out of the transaction. This means that you could mess up your identity when you use it by mistake on such table. Is this correct?



October 19, 2010 4:38 PM

Dale said:

But if the transaction is committed, then you cannot roll it back, correct?

October 27, 2010 4:47 PM

Kalen Delaney said:

Arjan... what do you mean by 'mess up? There is no way that part of a row could be 'out' of the transaction. When you truncate a table with identity, the identity is reset.

Dale... COMMIT is the opposite of ROLLBACK. If you do one, you can't do the other.

October 27, 2010 7:19 PM

Paul Williams said:

Thank you for logging this.  I never realized TRUNCATE TABLE could be rolled back.

November 1, 2010 2:11 PM

Raj said:


suppose we have done commit truncate table ..but can we recover that trucate table through  transactional logged backup ? as we can do that for deleted rows ...

September 22, 2012 4:08 AM

Kalen Delaney said:

Hi Raj

We can use a log backup to recover any changes by just restoring to a time BEFORE the change took place, whether it's a delete, truncate or drop table, it doesn't matter. The restore does not actually undo anything.. it just brings the database back to a point before you removed the data.


September 22, 2012 8:33 PM

king said:



January 16, 2018 11:31 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:44 PM

chenyingying said:


May 6, 2018 10:48 PM

linying123 said:


May 10, 2018 8:09 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement