Why does TRUNCATE TABLE hang?

Question

Why does TRUNCATE TABLE hang?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

Allen Nugent (10/28/2012)
Thanks Gail.
My tables are still locked. The database is now useless to me.

There is a fault that I've experienced in SQL Server with killing spids depending on what the spids are doing. For example, if you have work-in-progress or an open transaction against an AS400 linked server, killing the spid will set the spid to a rollback mode.... forever. It also keeps a connection open on the AS400. If the rollback on the spid reports 0% complete for hours, you'll also find that it's eating up some pretty good CPU time trying to rollback something that doesn't need to and can't rollback.

The only way that I've found to fix it (them) is to bounce the server.

With that thought in mind, try to never kill a spid... try several manual ROLLBACKs (or commits if you really want a commit) until you get the error that says there was nothing to rollback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Allen Nugent Right there with Babe Points: 734 More actions · Answer 2

Thanks, Jeff.

That sounds like the right diagnosis. The query that caused the locks was in SQL Server 2008 R2, but it was reading from tables on a SQL Server 7 database on a Windows 2000 machine (!)

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

Allen Nugent (10/28/2012)
Thanks, Jeff.
That sounds like the right diagnosis. The query that caused the locks was in SQL Server 2008 R2, but it was reading from tables on a SQL Server 7 database on a Windows 2000 machine (!)

You may end up having to bounce both machines. I don't remember exactly where I saw it (I think it may have been a CONNECT item) but all MS kept saying was to "call customer support". The people that I know that have had this problem spent a couple of hours on the blower with MS Support and it ended with the recommendation to bounce the servers involved. I say cut out the middle man and bounce the servers. Then, put the word out that no one kills a spid unless they've exhausted the ROLLBACK/COMMIT suggestion I made. If you aren't sure which machine is the cause, take the time to find it 'cause it's worth finding it and doing the graceful exit thing instead of having to bounce a 24/7 server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 4

I've had the same symptom occur on a SQL 2012 machine. SSMS Tools was installed which has an option to block TRUNCATE statements, so that might be worth checking if anyone else is getting a similar issue.

--
Scott

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

Try DBCC OPENTRAN to view any open transaction the machine you are currently connected to.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 6

Scott-144766 (5/7/2015)
I've had the same symptom occur on a SQL 2012 machine. SSMS Tools was installed which has an option to block TRUNCATE statements, so that might be worth checking if anyone else is getting a similar issue.

TRUNCATE isn't the problem. Having open transactions on the table when trying to do a TRUNCATE is. Killing SPIDs without understanding is also a problem but TRUNCATE isn't the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)