Can''t kill a process / get rid of a lock
-
I am pretty sure I had a deadlock on my 2000 DB when I couldn't even refresh the process list. So not being able to get into the process list to kill the process, I stopped and started the agent.
Now I can view the processes again, but there's one process I can't kill. It says it has a lock on the master and is performing a select into.
I've tried to right click the process to kill it. I've tried typing kill 53 into SQL analyzer to kill it. Although the second method said it completed, the process still shows up in the list of processes.
How do I kill this thing?
-
Is it coming from your connection?
It could be the process which populates the process info window.
-
Yes the client connection is myself, but usually the status is 'awaiting command' not 'select into'.
-
Ok the problem is it is holding a lock on part of the master db. I queried the tembdb lock info (select * from ##lockinfo53), and here are the results:
Process ID DBID Database ObjID Object Table ObjOwner IdxID Index Lock Type Mode Status Owner Resource 51 7 prosightD1 0 prosightD1 0 DB S GRANT Sess 52 4 msdb 0 msdb 0 DB S GRANT Sess 53 1 master 85575343 master.dbo.spt_values spt_values dbo 0 TAB Sch-S GRANT Xact 53 2 tempdb 2073774445 tempdb.dbo.##lockinfo53 ##lockinfo53 dbo 0 ##lockinfo53 TAB X GRANT Xact 54 5 FleetTest 0 FleetTest 0 DB S GRANT Sess I've never seen a process hold a lock for this amount of time, so I would imagine I need to somehow release the lock.
-
I found additional info regarding locks on spt_values and a lockinfo[spid] for this while searching the web (Go Google!):
The person's question was what are these two locks:
------------
Object:master.dbo.spt_values
Lock Type:TAB
Mode:Sch-S
Status:GRANT
Owner:Xact
Index:
Object:tempdb.dbo.##lockinfo51
Lock Type:TAB
Mode:X
Status:GRANT
Owner:Xact
Index:##lockinfo51
------------
(Ah doesn't that look familiar) He was looking for locks on the database (just as I am) And here is the answer:
------------
Those entries you are looking at
in Enterprise Manager are for your connection when you are
checking the locks using Enterprise Manager. What you are
seeing is normal for what you are doing.
------------
So it sounds like the locks I'm worried about are my own locks I'm putting on the DB by looking for locks...
-
Ok I'm not out of the woods yet. My templog is 40 gigs...
I did verify the lock problem I was having is indeed not a problem by running sp_who, which showed spid 53 as awaiting command (because I was no longer looking at locks or processes)
But what is causing this massive templog?
-
Jon,
It looks like you are now getting process details using sp_who and sp_who2, which is often what you need to do. This is because the process display in SQL Server 2000 Enterprise Manager often becomes unresponsive when SQL is busy.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply