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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.