db locks

Hello,

I found a dead lock in my SQLServer 2000 is spid69 & spid70 as shown in the following link:

http://www.geocities.com/belal_marzouk/support/sql2000/db_lock/index.html

1 - The first screen lists alot of sleeping processes, is this normal case to have all of these proecess sleeping at a time.

2 - The second & third screens shows details of the blocking proecesses. Kill process can not stop them, how can I release this lock and prevent it to happen in the future?

bmarzoukAsked:
Who is Participating?
 
ewahnerCommented:
Here is a script I use to check What Statements are actually blocking and who they are blocking.  Run this in Query Analyzer.  Set your results to Text CTRL-T.  Also I would not go killing any spids as that could lead to database corruption.  Not a good idea.  

Blocking is not "GOOD" or "NORMAL".  It is a symptom of a larger problem, improper use of indexes or missing indexes.  Table Scans, Index Scans you name it.  Also Blocking usually leads to a DEADLOCK.  So it is good to run this script every so often to see whats causing it.  Enterprise manager will not work for discovering the actual query behind the block, this will.

----- begin cut ------
set nocount on
declare @spid varchar(10)
declare @blkby varchar(10)
declare @stmt varchar(100)
if not exists ( select top 1 name from tempdb..sysobjects where name like '#temp%' )
begin
   create table #temp ( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),
                        dbname varchar(25), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25),
                        programname varchar(255), spid2 integer )
end
else
begin
   truncate table #temp
end
insert into #temp
exec sp_who2

declare curs cursor for
select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby
while @@fetch_status = 0
begin
   set @stmt = 'dbcc inputbuffer(' + @blkby + ')'
   raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait
   exec (@stmt)
   raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait
   set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'
   exec (@stmt)
   fetch next from curs into @spid, @blkby
end

close curs

deallocate curs
----- end cut ------
0
 
lluthienCommented:
not actually a deadlock,
as process 70 is not blocked.

it should be running normally

only 69 is blocked.

or am i missing something?
0
 
bmarzoukAuthor Commented:
OK, I agree it is not a dead lock ... could you please answer my questions?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lluthienCommented:
basically you need to know what proces 70 is doing and what is taking him so long.
you could circumvent the blocking mechanism by using a different isolation level
but the block is here for a (probably good) reason.

try to figure out if proces 70 is waiting for something, stuck in a loop or maybe just doing a lot of work

furthermore, i dont really see why
kill 70
doesnt work.

0
 
ewahnerCommented:
Oh yeah and the sleeping processes are most likely system processes...don't touch them.
0
 
bmarzoukAuthor Commented:
The script returns the following message

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
0
 
ewahnerCommented:
Hmm...make sure you are a member of the sysadmin role.
0
 
bmarzoukAuthor Commented:
I run the script as "sa"
0
 
ewahnerCommented:
Make sure you can at least do a sp_who2.  What ver of SQL are you using?
0
 
bmarzoukAuthor Commented:
It works now but there is no dead lock as I have restarted the db server. I will try it when deadlock occus again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.