patternsqlModerate
What factors prevent and delay TRUNCATE command?
Viewed 0 times
factorspreventdelaywhattruncateandcommand
Problem
I had a quite large table, perhaps a 50M rows and I had to truncate it. Usually truncate is really fast but in this case it took hours to complete. What are the factors that can prevent
truncate command behaving normally?Solution
Other queries. A TRUNCATE statement has to wait until all SELECT statements from that table to complete and as well it must wait until all other transactions that issued INSERT, UPDATE, DELETE, MERGE statements on the table being truncated to commit or rollback. Also other DDL transactions (ALTER TABLE) have to commit or rollback before TRUNCATE can proceed.
Context
StackExchange Database Administrators Q#15540, answer score: 11
Revisions (0)
No revisions yet.