I want to TRUNCATE (or remove all rows in) a table with 50 million rows.

The database is in use in production.

How long time will the TRUNCATE operation take?

  • 1
    I can't tell you how long it will take on your system other than to say it should be reasonably fast as long as it's not blocked by other activity because the physical space deallocation is deferred. See the doc for more details. – Dan Guzman Jun 12 '19 at 10:00

In normal circumstances this should take very little time since it is a metadata change. Whereas a regular delete will delete all data row by row, a truncate operation will deallocate all data pages instead.

Be aware though that a truncate table requires a Sch-M lock, so on a busy environment it could take a while before it can get hold of its lock and truncate the data. If your statement keeps running for a long time, it's probably locked by (an)other transaction(s).

If you want to be sure, try it on a copy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.