Tell me more ×
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's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question
4  
Often worth looking at sys.dm_os_waiting_tasks when you encounter mysterious delays to see what the wait type and resource is. In this case I presume it would have been a Sch-M lock on the table. – Martin Smith Mar 25 '12 at 21:28

1 Answer

up vote 11 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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