SQL Server Myths :- Truncate command Can’t Be Rolled Back and TRUNCATE Is Non-logged?

• There is no such thing as a no-logged operation in a user databases.A TRUNCATE TABLE operation delete of all data in the table.
• The fact that TRUNCATE TABLE uses fewer transaction log resources.

Let’s Investigate that Can TRUNCATE TABLE be rolled back?

Here I am going to give you one demo where we will truncate table and show you TRUNCATE TABLE operation is logged and rollback.

example script:-

CREATE TABLE testdemo (Id INT IDENTITY, name CHAR (80) DEFAULT 'ABCD');

INSERT INTO testdemo DEFAULT VALUES;
GO 1000

BEGIN TRANSACTION;

SELECT COUNT(*) [Starting_Table_Row_Count] FROM testdemo

TRUNCATE TABLE testdemo

SELECT COUNT(*) [Row_Count_After_Truncate] FROM testdemo

ROLLBACK TRANSACTION;

SELECT COUNT(*) [Row_Count_After_Rollback] FROM testdemo

After execution of above scripts you will get the output like below:-

scripts and output shown above proved that Truncate command is roll backed.

 

======== Proving that TRUNCATE  is a logged command ========================

Use Truncate_Demo;

CHECKPOINT;

SELECT COUNT(*) [Starting_Log_Row_Count]
FROM sys.fn_dblog (NULL, NULL);

TRUNCATE TABLE testdemo;

SELECT COUNT(*) [Log_Row_Count_After_Truncate]
FROM sys.fn_dblog (NULL, NULL);

 

 

 

SELECT
[Current LSN], [Operation], [Context],
[Transaction ID], [AllocUnitName], [Transaction Name]
FROM fn_dblog (NULL, NULL);
GO

After execution of above script you will get the out like below where you are able to find the TRUNCATE COMMAND log.

Hence we can say that TRUNCATE TABLE is a logged command and can be rolled back.