SQL Server 2005 – How Long Will TRUNCATE TABLE Take on 252M Rows (170GB Data)?

sql serversql-server-2005truncate

I understand that there are many variables in this and am not looking for an exact number of min:sec 🙂 I've read many resources that state that a TRUNCATE uses far less resources than a DELETE and hence will perform faster. But there are no order of magnitudes mentioned. As a TRUNCATE is a DDL (Data Definition Language) operation and not a DML (Data Manipulation Language) operation and only the system tables are part of the transaction, I'm presuming that it should not take very long.

Anyone with experience on doing this with a table anywhere near this size that can lend some insight? We talking up to a minute, up to an hour, days, weeks?

Best Answer

A millisecond or so.

Truncate is O(1) - a pure metadata operation.

This is assuming there is no concurrent activity on the table.

Truncate will potentially need to wait until existing reads are done to acquire a Sch-M (schema modification) lock.

For large tables, the actual deallocation is done asynchronously on a background thread (SQL Server 2000 SP3 onward).

For some other myths surrounding truncate, see A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged by Paul S. Randal.

Related Solutions

Mysql – What can cause TRUNCATE TABLE to take a really long time

Using TRUNCATE TABLE on an InnoDB table requires a full table lock because TRUNCATE TABLE is DDL (Data Definition Language) not DML (Data Manipulation).

Doing DELETE FROM user_engagements; will not help because MVCC info is written to the undo logs in ibdata1, and that can hold up the table from being emptied. If any uncommitted transactions are holding onto user_engagements, that could potentially hold up a TRUNCATE TABLE as well.

You could rename the table so that it is immediately available

SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE user_engagements_new LIKE user_engagements;
ALTER TABLE user_engagements RENAME user_engagements_zap;
ALTER TABLE user_engagements_new RENAME user_engagements;
DROP TABLE user_engagements_zap;
SET FOREIGN_KEY_CHECKS = 1;

This should replicate quickly except for the last statement.

Give it a Try !!!

If you have MySQL 5.1.16+, TRUNCATE TABLE requires DROP privilege. My answer performs what TRUNCATE TABLE now does.

If you have MySQL 5.1.15 and back, you need DELETE privilege, which my answer covers.

SQL Server – Slow INSERT/SELECT After TRUNCATE TABLE

update statistics tablename. Enjoy.

Best Answer

Related Solutions

Mysql – What can cause TRUNCATE TABLE to take a really long time

SQL Server – Slow INSERT/SELECT After TRUNCATE TABLE

Related Question