Blocking - Why would a delete statement block itself?

Question

Blocking - Why would a delete statement block itself?

DBADave

SSChampion

Points: 10962
More actions
April 15, 2008 at 9:52 pm

#121402

I'm running a delete statement for a developer and noticed the statement is blocking itself. I see SPID 160 blocking SPID 160. Why?
Print 'Before DELETE - 2002-12-31 00:00:00'
Select count(*)
FROM tblFinalArchive
WHERE (dTimeSent < CONVERT(DATETIME, '2002-12-31 00:00:00', 102))
DELETE FROM tblFinalArchive
WHERE (dTimeSent < CONVERT(DATETIME, '2002-12-31 00:00:00', 102))
Print 'After DELETE - 2002-12-31 00:00:00'
Select count(*)
FROM tblFinalArchive
WHERE (dTimeSent < CONVERT(DATETIME, '2002-12-31 00:00:00', 102))
Thanks, Dave

Viewing 5 posts - 1 through 5 (of 5 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

A process shows up as blocking itself when it is waiting on resources (usually I/O). Take a look at the lastwaittype from sysprocesses for that spid and it will tell you what that process is waiting on.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

DBADave SSChampion Points: 10962 More actions · Answer 2

Thanks. Sounds like I may have a memory or disk bottleneck. I do see some paging issues so I'll problably add some memory to see if this addressess the problem.

Dave

DBADave SSChampion Points: 10962 More actions · Answer 3

Upon further review...

The primay issue is expected SQL 2000 SP4 behavior according to http://support.microsoft.com/kb/906344.

Thanks, Dave

John Rowan SSC Guru Points: 56440 More actions · Answer 4

So what was the waittype of your self-blocked process?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden