Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

db locks

Posted on 2006-05-04
10
Medium Priority
?
683 Views
Last Modified: 2008-03-10
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?

0
Comment
Question by:bmarzouk
  • 4
  • 4
  • 2
10 Comments
 
LVL 11

Expert Comment

by:lluthien
ID: 16604019
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
 

Author Comment

by:bmarzouk
ID: 16604140
OK, I agree it is not a dead lock ... could you please answer my questions?
0
 
LVL 11

Expert Comment

by:lluthien
ID: 16604495
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 2

Accepted Solution

by:
ewahner earned 2000 total points
ID: 16604996
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
 
LVL 2

Expert Comment

by:ewahner
ID: 16605002
Oh yeah and the sleeping processes are most likely system processes...don't touch them.
0
 

Author Comment

by:bmarzouk
ID: 16606808
The script returns the following message

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
0
 
LVL 2

Expert Comment

by:ewahner
ID: 16683257
Hmm...make sure you are a member of the sysadmin role.
0
 

Author Comment

by:bmarzouk
ID: 16683331
I run the script as "sa"
0
 
LVL 2

Expert Comment

by:ewahner
ID: 16683353
Make sure you can at least do a sp_who2.  What ver of SQL are you using?
0
 

Author Comment

by:bmarzouk
ID: 16683389
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question