ROLLBACK after TRUNCATE TABLE in a Transaction  

Posted by ReelTym

if object_id( 'Testing123' ) is not null
    drop table dbo.Testing123;
go

create table Testing123 ( ordinal int identity(1,1) primary key clustered, data varchar(max) )
go

insert into Testing123 ( data )
    select o1.name + ' - ' + o2.name
        from sys.objects o1
        cross join sys.objects o2
go

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

select count(*) from dbo.Testing123; 
go 
dbcc ind('Test', 'Testing123', -1); 
go

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

/*
(3025 row(s) affected)

-----------
3025

(1 row(s) affected)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       154         NULL   NULL        21575115    1           1               72057594038845440    In-row data          10       NULL       0           0           0           0
1       153         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           156         0           0
1       155         1      154         21575115    1           1               72057594038845440    In-row data          2        1          0           0           0           0
1       156         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           157         1           153
1       157         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           158         1           156
1       158         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           159         1           157
1       159         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           168         1           158
1       168         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           169         1           159
1       169         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           176         1           168
1       176         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           177         1           169
1       177         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           178         1           176
1       178         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           179         1           177
1       179         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           180         1           178
1       180         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           181         1           179
1       181         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           182         1           180
1       182         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           183         1           181
1       183         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           184         1           182
1       184         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           185         1           183
1       185         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           186         1           184
1       186         1      154         21575115    1           1               72057594038845440    In-row data          1        0          0           0           1           185

(20 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

--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.

begin tran 
    truncate table dbo.Testing123; 

    insert into Testing123 ( data )
        select o1.name
            from sys.objects o1

    select count(*) from dbo.Testing123;

    dbcc ind('Test', 'Testing123', -1); 
    dbcc extentinfo('Test', 'Testing123', -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('Test');

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

/*

(55 row(s) affected)

-----------
55

(1 row(s) affected)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       155         NULL   NULL        149575571   1           1               72057594038976512    In-row data          10       NULL       0           0           0           0
1       154         1      155         149575571   1           1               72057594038976512    In-row data          1        0          0           0           0           0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
file_id     page_id     pg_alloc    ext_size    object_id   index_id    partition_number partition_id         iam_chain_type       pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1           154         1           1           149575571   1           1                72057594038976512    In-row data          0x6000000000000000

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
resource_type                                                resource_description                                                                                                                                                                                                                                             request_mode
------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
PAGE                                                         1:155                                                                                                                                                                                                                                                            X
PAGE                                                         1:153                                                                                                                                                                                                                                                            X
PAGE                                                         1:182                                                                                                                                                                                                                                                            X
PAGE                                                         1:183                                                                                                                                                                                                                                                            X
PAGE                                                         1:180                                                                                                                                                                                                                                                            X
PAGE                                                         1:181                                                                                                                                                                                                                                                            X
PAGE                                                         1:178                                                                                                                                                                                                                                                            X
PAGE                                                         1:179                                                                                                                                                                                                                                                            X
PAGE                                                         1:176                                                                                                                                                                                                                                                            X
PAGE                                                         1:177                                                                                                                                                                                                                                                            X
EXTENT                                                       1:176                                                                                                                                                                                                                                                            X
EXTENT                                                       1:216                                                                                                                                                                                                                                                            X
EXTENT                                                       1:208                                                                                                                                                                                                                                                            X

(13 row(s) affected)

*/

--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.

    rollback tran; 
go 
select count(*) from dbo.Testing123; 
dbcc ind('Test', 'Testing123', -1); 
go

/*

-----------
3025

(1 row(s) affected)

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       154         NULL   NULL        21575115    1           1               72057594038845440    In-row data          10       NULL       0           0           0           0
1       153         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           156         0           0
1       155         1      154         21575115    1           1               72057594038845440    In-row data          2        1          0           0           0           0
1       156         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           157         1           153
1       157         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           158         1           156
1       158         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           159         1           157
1       159         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           168         1           158
1       168         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           169         1           159
1       169         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           176         1           168
1       176         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           177         1           169
1       177         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           178         1           176
1       178         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           179         1           177
1       179         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           180         1           178
1       180         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           181         1           179
1       181         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           182         1           180
1       182         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           183         1           181
1       183         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           184         1           182
1       184         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           185         1           183
1       185         1      154         21575115    1           1               72057594038845440    In-row data          1        0          1           186         1           184
1       186         1      154         21575115    1           1               72057594038845440    In-row data          1        0          0           0           1           185

(20 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

This entry was posted on Friday, November 05, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments