Truncate speed

[Naith]

Hey guys,

On SQL 8, I have a table with around 500k rows. The table has one key, a clustered index, and a few other indices.

Yet a truncation takes over two hours.

Why would a truncation take this long?

 

[SQLDenis]

probably blocking/locking by other processes

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Naith]

Okay, elementary, I guess. I should have said, there are no other locks/blocks on the table. I have exclusive use of the processes, and this session is the only one.

 

[mrdenny]

Disk issues perhaps.

What does the sp_who2 and sysprocesses table say is the reason for the wait?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Naith]

I brought the machine down and restarted the process before I checked. It seemed to release whatever it was that was causing the issue. Truncate < 1 sec.

Thanks for your help.

 

[onpnt]

Even knowing your reboot worked, try to find the problem prior to going to those measures again next time. If it happened once the events that surrounded the problem will surely reappear and you'll be back to square one. In a high availability environment a reboot is never an acceptable answer. I have a feeling mrdenny's suggestion would have shown you the answer and the solution without taking down your environment for even a 4-5 minute time period

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}