GAPTHEGURU

Geek with special skills

SQL SERVER – Rollback TRUNCATE Command in Transaction

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

The code to simulate above result is here.

USE tempdb

GO

-- Create Test Table

CREATE TABLE TruncateTest (ID INT)

INSERT INTO TruncateTest (ID)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

GO

-- Check the data before truncate

SELECT * FROM TruncateTest

GO

-- Begin Transaction

BEGIN TRAN

-- Truncate Table

TRUNCATE TABLE TruncateTest

GO

-- Check the data after truncate

SELECT * FROM TruncateTest

GO

-- Rollback Transaction

ROLLBACK TRAN

GO

-- Check the data after Rollback

SELECT * FROM TruncateTest

GO

-- Clean up

DROP TABLE TruncateTest

GO

Example from sql authority:

Following example demonstrates how during the transaction truncate can be rolled back.

 

Advertisements

01/31/2012 - Posted by | SQL Scripting, Sql Server, T-SQL | , , , , ,

2 Comments »

  1. looks like copied as is from http://blog.sqlauthority.com/2010/03/04/sql-server-rollback-truncate-command-in-transaction/

    Comment by Prakash Pulak | 06/18/2012 | Reply

    • Hi,
      I use my blog also as my online toolbox, so some info that i would like to remember is listed. I had a similar problem and found that info and modified with my result. I forgot to mention this in the beginning of this post.
      Thank you for reminding me.

      Comment by gaptheguru | 06/18/2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: