TRUNCATE vs DELETE in SQL Server

A version of this runs somewhere every week. The purge job fires at 02:00 like it has for three years. By 02:40 the log has grown an order of magnitude, the volume is full, and every session touching dbo.AuditLog waits behind one DELETE. The on-call DBA kills it. Rollback takes longer than the delete did, and the log file stays at its new size.

That job used DELETE because somebody on the team weighed TRUNCATE vs DELETE and remembered that TRUNCATE cannot be rolled back, and a purge that cannot be undone is not a purge anyone signs off on. That belief is wrong, and so is the other one people repeat about TRUNCATE. Correct both first, because they steer people to the statement that hurt them.

Everything below applies to SQL Server 2008 through 2022 and to SQL Server 2025 unless a version is named, on Standard and Enterprise both. The exceptions worth flagging up front: partition truncation and the system-versioned temporal restriction are 2016 and later, CREATE OR ALTER needs 2016 SP1, the EDGE constraint restriction is 2017 and later, and backup compression on Standard starts at 2008 R2.

"TRUNCATE is not logged" is false

Microsoft's own page states the mechanism in one sentence: "The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table and index data and records only the page deallocations in the transaction log."

Records only the page deallocations. The statement writes log. Paul Randal measured how much. In his test a TRUNCATE against a quiet database took the log from 2 records to 541, and the count kept climbing afterward as a background task finished the deallocations.

Run the measurement yourself. sys.fn_dblog is undocumented and unsupported, so use it on a test instance, and drop any column below that errors on your build:

CREATE DATABASE TruncTest;
GO
ALTER DATABASE TruncTest SET RECOVERY SIMPLE;
GO
USE TruncTest;
GO

CREATE TABLE dbo.Wide
(
    Id     int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Wide PRIMARY KEY CLUSTERED,
    Filler char(500) NOT NULL DEFAULT REPLICATE('x', 500)
);

INSERT dbo.Wide (Filler)
SELECT TOP (200000) REPLICATE('x', 500)
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
GO

CHECKPOINT;
GO
SELECT COUNT(*) AS RecordsBefore, SUM([Log Record Length]) AS BytesBefore
FROM sys.fn_dblog(NULL, NULL);

Delete every row, then look again:

DELETE dbo.Wide;

SELECT COUNT(*) AS RecordsAfterDelete, SUM([Log Record Length]) AS BytesAfterDelete
FROM sys.fn_dblog(NULL, NULL);

The count climbs past 200,000. Every row generates an LOP_DELETE_ROWS record carrying the row image, which is why the log bytes land in the same order of magnitude as the data. Reload the table, CHECKPOINT, and run TRUNCATE TABLE dbo.Wide instead. The record count lands in the hundreds. The breakdown shows where it went:

SELECT
    [Transaction Name],
    Operation,
    COUNT(*)                  AS RecordCount,
    SUM([Log Record Length])  AS LogBytes
FROM sys.fn_dblog(NULL, NULL)
GROUP BY [Transaction Name], Operation
ORDER BY LogBytes DESC;

Two transaction names appear. TRUNCATE TABLE is the statement itself. DeferredAllocUnitDrop::Process is the background task that releases the extents afterward, which is why the space does not come back the instant the statement returns. Wait a few seconds and query fn_dblog again: the count grows.

The rule worth remembering: DELETE log volume scales with rows and with the indexes touched, TRUNCATE log volume scales with extents, and those are different quantities. Fifty million rows at 40 bytes fit about 191 to a page, near 32,700 extents. The same rows at 400 bytes fit about 20 to a page, near 312,500 extents, ten times the truncate cost. Add four nonclustered indexes to the narrow table and the DELETE cost climbs again while the truncate cost stays put.

"TRUNCATE cannot be rolled back" is also false

"A TRUNCATE TABLE operation can be rolled back within a transaction." That is Microsoft's wording, and the demonstration takes ten seconds:

SELECT COUNT(*) AS BeforeTruncate FROM dbo.Wide;   -- 200000

BEGIN TRANSACTION;
    TRUNCATE TABLE dbo.Wide;
    SELECT COUNT(*) AS InsideTransaction FROM dbo.Wide;  -- 0
ROLLBACK TRANSACTION;

SELECT COUNT(*) AS AfterRollback FROM dbo.Wide;    -- 200000

All 200,000 rows come back. This is the point that kills the loose phrase "minimally logged," which people reach for as a softer version of "not logged." Minimal logging is a term of art in SQL Server and it has a published membership list. Microsoft's "Operations That Can Be Minimally Logged" names bulk import, SELECT INTO, partial updates through the .WRITE clause, WRITETEXT and UPDATETEXT, CREATE INDEX, ALTER INDEX REBUILD, and the heap rebuild from DROP INDEX. TRUNCATE TABLE is not on it.

The mechanism explains why. A minimally logged operation records allocations without the contents, so a rollback can deallocate but cannot restore. TRUNCATE restores. A community-authored TechNet Wiki article runs both discriminating tests, the rollback and a point-in-time restore through the truncate, and both succeed: "the fact that the rollback was able to restore all the records back into the table is self-evident that truncate is not a minimally-logged but a fully-logged operation." Treat that page as a worked demonstration rather than as Microsoft's position. Microsoft's position is the list.

Say it this way instead: TRUNCATE is fully logged. It logs deallocations rather than rows, so it writes far less log than an equivalent DELETE, and the recovery model changes none of that. TRUNCATE in SIMPLE and TRUNCATE in FULL write the same records. The difference is that in FULL the log cannot be reused until a log backup captures them.

The myth has an origin: a committed TRUNCATE is done, and an autocommit batch commits the moment the statement finishes. That is also true of DELETE, which is why it never earned the same reputation.

The differences that matter

Identity. TRUNCATE resets the identity counter to the seed, defaulting to 1 when no seed was declared. DELETE leaves the counter where it stands. Reseeding after a DELETE takes DBCC CHECKIDENT, and the semantics differ depending on how the table was emptied. Microsoft's wording: "If no rows are inserted into the table since the table was created, or if all rows are removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. If rows are present in the table, or if all rows are removed by using the DELETE statement, the next row inserted uses new_reseed_value + the current increment value."

Which means DBCC CHECKIDENT('dbo.Wide', RESEED, 1) after a DELETE gives you a first row of 2. After a TRUNCATE it gives you 1. Reseed to 0 after a DELETE if you want the sequence to start at 1:

DELETE dbo.Wide;
DBCC CHECKIDENT ('dbo.Wide', RESEED, 0);
INSERT dbo.Wide (Filler) VALUES (REPLICATE('x', 500));
SELECT MAX(Id) FROM dbo.Wide;   -- 1

Triggers. TRUNCATE TABLE "can't activate a trigger because the operation doesn't log individual row deletions." A FOR DELETE or AFTER DELETE trigger that maintains an audit table, a denormalized count, or a downstream queue gets bypassed with no error and no warning. There is no DDL trigger fallback either. A Microsoft support blog states it: "there is no TRUNCATE TABLE event defined in SQL Server. And, while a truncation is effectively a drop and recreate of the table, it does not fire the DROP TABLE event. Thus, a DDL trigger will not work."

If a table's correctness depends on a delete trigger, TRUNCATE is out for that object however fast it is.

Permissions. DELETE needs the DELETE permission. TRUNCATE needs ALTER on the table, a much bigger grant, and it is not transferable: "TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and aren't transferable." The workaround is a procedure with EXECUTE AS, so the application login gets EXECUTE and nothing else:

CREATE OR ALTER PROCEDURE dbo.Staging_Clear
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.Staging_Orders;
END
GO

GRANT EXECUTE ON dbo.Staging_Clear TO AppRole;

Locks. "TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page, but not each row." A Sch-M lock conflicts with the Sch-S lock that every query takes, including queries running under NOLOCK. A TRUNCATE that sits inside an open transaction for four minutes blocks every reader of that table for four minutes, and READ_COMMITTED_SNAPSHOT does not save you, because row versioning has nothing to say about schema locks.

DELETE starts at row granularity and gives readers a chance. It gives that chance up at scale, which is the next section.

What blocks TRUNCATE

Microsoft lists five conditions. You cannot truncate a table that:

• is referenced by a FOREIGN KEY constraint, with the exception that "You can truncate a table that has a foreign key that references itself"
• participates in an indexed view
• is published by transactional replication or merge replication
• is system-versioned temporal
• is referenced by an EDGE constraint

The foreign key rule catches people because disabling the constraint does not help. Microsoft's page lists the restriction and says nothing about disabled constraints either way, so what follows is reproducible but undocumented. Two community sources report it and the repro takes a minute. Run it on your build before you write a procedure around it:

CREATE TABLE dbo.Parent (Id int NOT NULL CONSTRAINT PK_Parent PRIMARY KEY);
CREATE TABLE dbo.Child
(
    Id       int NOT NULL CONSTRAINT PK_Child PRIMARY KEY,
    ParentId int NOT NULL CONSTRAINT FK_Child_Parent REFERENCES dbo.Parent (Id)
);

ALTER TABLE dbo.Child NOCHECK CONSTRAINT FK_Child_Parent;

SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys WHERE name = 'FK_Child_Parent';
-- is_disabled = 1

TRUNCATE TABLE dbo.Parent;
-- Msg 4712, Level 16, State 1
-- Cannot truncate table 'dbo.Parent' because it is being referenced by a FOREIGN KEY constraint.

The constraint has to be dropped, not disabled. NOCHECK suppresses validation of new DML on the child table; it does not remove the dependency the engine checks before a truncate. Plan the drop and recreate, and remember that recreating with WITH NOCHECK leaves the key untrusted, which changes the plans the optimizer will consider.

Change data capture is not in the documented list but produces its own error. The number is not on a Microsoft Learn errors page, so confirm it on your build. Pinal Dave reproduced the text:

-- Msg 4711, Level 16, State 1
-- Cannot truncate table 'dbo.Orders' because it is published for replication
-- or enabled for Change Data Capture.

That error covers replication and CDC together. Check before you write the purge job:

SELECT
    t.name,
    t.is_replicated,
    t.is_tracked_by_cdc,
    t.temporal_type_desc,
    ReferencingFKs = (SELECT COUNT(*) FROM sys.foreign_keys fk
                      WHERE fk.referenced_object_id = t.object_id
                        AND fk.parent_object_id <> t.object_id)
FROM sys.tables AS t
WHERE t.name = 'Orders';

The rule runs the other direction too. If a table must never be truncated, give it a referencing constraint on purpose. Microsoft's own guidance: "if no logical relationship exists, we could create an empty table simply for the purpose of creating a foreign key." It converts an accidental truncate into error 4712.

Truncating one partition

SQL Server 2016 added partition targeting to TRUNCATE TABLE, and it is the best reason to partition a purge table:

TRUNCATE TABLE dbo.AuditLog WITH (PARTITIONS (2));
TRUNCATE TABLE dbo.AuditLog WITH (PARTITIONS (1, 5));
TRUNCATE TABLE dbo.AuditLog WITH (PARTITIONS (2, 4, 6 TO 8));

Before 2016 a sliding window purge meant building a staging table with matching structure and indexes on the same filegroup, switching the partition out, then dropping the staging table. Microsoft's SQLCAT team on why the syntax exists: "If the goal is simply to delete the data from the partition, then the programming needed for creating the staging table and switching partition may be cumbersome."

Partition numbers are not month numbers and they shift as you split and merge, so resolve them rather than hard-coding:

DECLARE @Cutoff datetime2(0) = DATEADD(MONTH, -13, SYSUTCDATETIME());

DECLARE @Partitions nvarchar(max) =
    STUFF((SELECT ',' + CAST(p.partition_number AS varchar(10))
           FROM sys.partitions AS p
           JOIN sys.indexes  AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
           WHERE p.object_id = OBJECT_ID('dbo.AuditLog')
             AND i.index_id IN (0, 1)
             AND p.rows > 0
             AND p.partition_number < $PARTITION.PF_AuditLog_Monthly(@Cutoff)
           ORDER BY p.partition_number
           FOR XML PATH('')), 1, 1, N'');

IF @Partitions IS NOT NULL
BEGIN
    DECLARE @sql nvarchar(max) =
        N'TRUNCATE TABLE dbo.AuditLog WITH (PARTITIONS (' + @Partitions + N'));';
    EXEC sys.sp_executesql @sql;
END

Table and index partitioning has been available on Standard Edition since SQL Server 2016 SP1, so this is not an Enterprise-only answer. The foreign key restriction still applies: a partition truncate on a table referenced by a constraint fails with the same 4712.

The large DELETE problem

Nobody searches for this comparison because they want to empty a staging table. They search for it because they have 50 million rows to remove from a table that stays online.

Three things break at once.

The log. A single DELETE of 50 million rows is one transaction, so nothing in the log can be reused until it commits or rolls back. Ben Johnston measured the effect at Simple Talk: the single-transaction delete grew the log from 100 MB to 115 GB, while the same work split into 10,000 row batches grew it to 1,060 MB. Two orders of magnitude, identical work. Recovery models, log sizing and what to do about a file that has already grown are a separate subject; start at when the purge fills the log instead if that is where you are.

Lock escalation. Microsoft's threshold: "A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index." Cross it and the row locks collapse into one exclusive table lock, and every reader taking a shared lock stops until the statement finishes. NOLOCK readers survive this one, because an X table lock is compatible with the Sch-S lock they take, which is the opposite of what happens under TRUNCATE. The engine keeps trying if the first attempt loses to a conflict: "If locks can't be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired."

The 5,000 figure is a floor, not a trigger point. Paul White traced the mechanism: "Lock escalation checking is triggered at 2500 held locks per transaction and every 1250 additional," and "These HoBt counts don't include the table-level intent lock or the current lock being acquired." So at the 5,000 check the per-index counter reads 4,998, the test fails, and the statement runs on to the next check point. His summary: "Simple tests will typically escalate at 6250 total held locks (where the single HoBt lock counter is 6248) despite the threshold being 5000."

Design to the documented 5,000 anyway. Escalation fires later than 5,000 and never sooner, so the number errs safe.

Blocking. Between the escalation and the length of the transaction, a 40 minute delete is a 40 minute outage on that table.

Watch it happen. Start a delete of 20,000 rows in one window, and run this in another:

SELECT
    resource_type,
    request_mode,
    request_status,
    COUNT(*) AS Locks
FROM sys.dm_tran_locks
WHERE request_session_id = 57          -- the deleting session
GROUP BY resource_type, request_mode, request_status
ORDER BY Locks DESC;

A row-locked delete shows thousands of KEY / X rows. An escalated one shows a single OBJECT / X row. After the fact, the counters are cumulative per index:

SELECT
    i.name AS IndexName,
    ios.index_lock_promotion_attempt_count,
    ios.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.AuditLog'), NULL, NULL) AS ios
JOIN sys.indexes AS i
    ON i.object_id = ios.object_id
   AND i.index_id  = ios.index_id;

The counters are per index, so a table with four nonclustered indexes has four of them. The clustered index trips first in most deletes, but a narrow nonclustered index covering the predicate can beat it there.

The batched delete

DECLARE @BatchSize int      = 4000,
        @Cutoff    datetime2(0) = '2026-06-19',
        @Rows      int      = 1,
        @Total     bigint   = 0;

WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.AuditLog
    WHERE EventDate < @Cutoff;

    SET @Rows = @@ROWCOUNT;           -- must be the statement immediately after the DELETE

    SET @Total += @Rows;

    IF @Rows > 0
        WAITFOR DELAY '00:00:00.500';
END

SELECT @Total AS RowsDeleted;

Four details carry the whole pattern.

@@ROWCOUNT has to be captured on the very next line. An IF, a PRINT or a SELECT in between resets it, and the loop then exits on the first pass or never exits. It terminates when a batch removes zero rows, meaning the predicate matched nothing. Do not terminate on a row count target: concurrent inserts move the boundary and you stop short.

Batch size and lock escalation interact. The threshold counts locks, not rows, and each index touched takes its own key locks on its own HoBt, so 4,000 sits under 5,000 with room for the locks the loop does not control. Test at 4,000, watch index_lock_promotion_count on every index, and lower it if a counter moves.

Every batch is its own transaction. Wrapping the WHILE in BEGIN TRANSACTION / COMMIT destroys the benefit, because the log cannot be reused until the outer transaction ends, and you are back to one giant transaction with extra steps. This is the most common way the pattern gets broken in review.

The WAITFOR DELAY gives readers a window between batches and gives availability group secondaries a chance to catch up. A purge that runs for hours will meet a deadlock or a full log, so consider wrapping the loop so a failure does not leave it half done before you schedule it.

One more condition that most write-ups skip: in FULL recovery, batching caps log growth only if log backups run often enough to make the space reusable between batches, which is the recovery model half of the problem and belongs with the log article above.

Two things to stop doing. The WITH (ROWLOCK) hint in most published batched-delete scripts does not prevent escalation: escalation counts locks per HoBt, and a granularity hint has no input into that count. Microsoft does not say this in so many words, so confirm it with index_lock_promotion_count. To turn escalation off on one table, ALTER TABLE dbo.AuditLog SET (LOCK_ESCALATION = DISABLE) "prevents lock escalation in most cases" rather than all, and it goes back on afterward. And do not run batches without an index on the predicate. Without one on EventDate, every one of the 12,500 batches scans the table to find 4,000 rows.

When you are deleting most of the table

At 90% deletion the batched loop is the wrong shape. Moving the survivors is cheaper than removing the casualties.

Copy the survivors and rename. Build the new table, load what stays, swap the names in a short transaction:

SELECT * INTO dbo.AuditLog_New
FROM dbo.AuditLog
WHERE EventDate >= '2026-06-19';

-- recreate every index, constraint, default and trigger on AuditLog_New here

BEGIN TRANSACTION;
    EXEC sys.sp_rename 'dbo.AuditLog',     'AuditLog_Old';
    EXEC sys.sp_rename 'dbo.AuditLog_New', 'AuditLog';
COMMIT TRANSACTION;

DROP TABLE dbo.AuditLog_Old;

SELECT INTO is minimally logged in SIMPLE and BULK_LOGGED, which is the actual saving. The trap is everything SELECT INTO does not copy: indexes, constraints, defaults, triggers, extended properties, compression settings, and permissions. Script them first from the original and apply them to the new table before the rename. Foreign keys pointing at the old table follow the object_id, so after the rename they reference AuditLog_Old, and the DROP TABLE on the last line fails until you drop them. Script them, drop them, and recreate them against the new table.

Switch out and truncate. The other pattern moves the whole table into a throwaway in one metadata operation:

-- AuditLog_Staging must be structurally identical, empty,
-- on the same filegroup, with matching indexes
ALTER TABLE dbo.AuditLog SWITCH TO dbo.AuditLog_Staging;
DROP TABLE dbo.AuditLog_Staging;

The current ALTER TABLE page documents the filegroup and empty-target rules and then says "Many additional restrictions apply to switching partitions" without listing them. The archived SQL Server 2005 documentation for partition switching does list them, and two of those lines still hold: "The source table cannot be referenced by a foreign key in another table" and "The source table and the target table cannot participate in a view with schema binding." Treat the rest of that page as 2005-era text, because at least one line on it has expired. "Neither the source table nor the target table can be sources of replication" no longer describes the product. Replication blocks SWITCH PARTITION by default and you can turn it on at the publication level with the @allow_partition_switch property on sp_addpublication or sp_changepublication, with @replicate_partition_switch controlling whether the DDL reaches subscribers.

This pattern gets recommended as the workaround for the foreign key restriction, and it is a partial one. A referencing constraint blocks the switch too:

Msg 4967, Level 16, State 1
ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table
'db.Sales.Orders' contains primary key for constraint 'FK_OrderLines_Orders'.

The difference from TRUNCATE is what clears it. Disabling the referencing constraint lets the switch through, where the same NOCHECK leaves TRUNCATE still raising 4712. Two practitioners published the repro, and it is the one real advantage SWITCH has here. Microsoft documents neither behavior, so run both halves yourself before you build a purge on the difference.

It is also a trap. The child rows that pointed at the switched-out data are orphans the moment the switch commits, and ALTER TABLE dbo.OrderLines WITH CHECK CHECK CONSTRAINT FK_OrderLines_Orders then fails on them. Use it when the children go in the same maintenance window, and not otherwise. With no foreign keys in play, what SWITCH buys you is emptying a table without holding a Sch-M lock for the length of a deallocation, and keeping the old rows while you verify.

Drop and recreate. Fastest of all and the one that costs the most to get wrong. You lose every grant, every foreign key in both directions, every trigger, index and statistic, and every plan on the old object_id recompiles. A SCHEMABINDING view blocks the drop outright, and a view without it returns errors until the table comes back. This belongs in a reviewed deployment script, not an ad hoc purge.

Choosing between TRUNCATE and DELETE

Situation	Statement	Reason
Empty a staging table between ETL runs	TRUNCATE TABLE	Deallocation cost, identity reset comes free
Empty a table that a delete trigger protects	DELETE	TRUNCATE does not fire the trigger
Empty a table referenced by a foreign key	DELETE, or drop the FK first	Error 4712, and disabling the constraint does not clear it
Empty a table under replication, CDC, or temporal versioning	DELETE	Error 4711 or the temporal restriction
Remove one month from a partitioned history table	TRUNCATE ... WITH (PARTITIONS (n))	2016 and later, no staging table required
Remove 5% of a large table while it stays online	Batched DELETE	Predicate needed, lock escalation avoidable
Remove 90% of a large table with a maintenance window	Copy survivors, rename	SELECT INTO is minimally logged
Remove everything and the table has no dependencies	TRUNCATE TABLE	Deallocation only, and the table survives with its grants and indexes
Reset an identity to 1	TRUNCATE, or DELETE plus DBCC CHECKIDENT(..., RESEED, 0)	Reseed semantics after each of the two differ by how you emptied it

The procedure for a large purge

1. Count what you are removing and what stays. SELECT COUNT_BIG(*) on both sides of the predicate. The ratio picks the strategy.
2. Query sys.tables for is_replicated, is_tracked_by_cdc, temporal_type_desc, and referencing foreign keys. Any of them rules out TRUNCATE before you go further.
3. Check for an index supporting the delete predicate. Create it if it is missing, and drop it afterward if nothing else uses it.
4. Confirm the recovery model. In FULL, schedule log backups every 5 minutes for the duration and confirm the backup target has room.
5. Record baseline index_lock_promotion_count for every index on the table.
6. Run one batch by hand. Time it, check the log growth with DBCC SQLPERF(LOGSPACE), and check the escalation counters again.
7. Size the batch so one iteration finishes in under a second and no counter moves. Start at 4,000 and work down.
8. Run the loop. Watch sys.dm_exec_requests for blocking and the log file for growth.
9. Rebuild the clustered index afterward if the table lost a large fraction of its rows. The batched delete leaves half-empty pages the batched loop leaves behind, which is a page density problem rather than a fragmentation one.
10. Update statistics on the table. A purge that removes 40% of the rows leaves histograms describing a table you just emptied, and the rebuild in step 9 covers the index statistics but not the column ones.

---

If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.