Yak wrote:
> Richard wrote:
> > hi yak,
> >
> > Thank u for the reply
> > > I am not sure about what you want to achieve by checking lock
> > > information in EM. Locks dynamically occur all the time and not
> > > necessarily bad things. Any database system won't work without some
> > > kind of lock mechanism, the key is how it is utilised.
> > >
> > > If you suspect some dead locks are happening, go check out the process
> > > info. You might be able to find out which process is blocking others.
> > >
> >
> > I will be more specific ,when i checked my EM for blocking .I found out
> > their were 3 SPID with blocking remark,when i checked its property i
> > found the following
> > 1) sp_cursoropen;1
> > 2) sp_cursorclose;1
> > 3) sp_cursorfetch;1
> > and this blocking is effecting my users who are connected to my server.
> > I googled a lot for the solution but their was no result.I want to
> > solve this problem for ever(if possible).
> >
> > :)
> > Richard
> >
> Richard,
>
> If you just want to kill the original culprit process, then go "process
> info" to find where the locks came from. You should be able to figure
> it out using "blocking" and "blockedby". Once you find it you can kill
> it from EM.
>
> To solve the problem "forever", I reckon you need to find out the
> reason for these locks. Usually locks will be released after the
> resource contention is resolved over times unlesss you have deadlocks
> there. (if you do, you can also find out using the solution as above)
>
> The easiest way is to monitor the application using profiles focusing
> on long running transactions (there must be some there if you always
> have locks not released for long time). You can find out the script the
> application uses which cause serious resource contention.
>
> Then talk to the programmer.
>
> At the moment, all you can do is to find out the problematic process
> and kill it.
>
> Cheers,
>
> Yak
> >
> > Yak wrote:
> > > Richard wrote:
> > > > hi,
> > > >
> > > > I am using SQL SERVER 2000.
> > > >
> > > > Problem that i am facing is when ever I check locks in Enterprise
> > > > Manager I find
> > > > following blocking - :
> > > > 1) sp_cursoropen;1
> > > > 2) sp_cursorclose;1
> > > > 3) sp_cursorfetch;1
> > > >
> > > > We dont have any control over application(source code).Whatever
> > > > we have to do, has to done from database side.
> > > >
> > > > can anyone guide me to solve this problem.
> > > >
> > > > Thanks
> > >
> > >
> > > Richard,
> > >
> > > I am not sure about what you want to achieve by checking lock
> > > information in EM. Locks dynamically occur all the time and not
> > > necessarily bad things. Any database system won't work without some
> > > kind of lock mechanism, the key is how it is utilised.
> > >
> > > If you suspect some dead locks are happening, go check out the process
> > > info. You might be able to find out which process is blocking others.
> > >
> > > If you just find you have got some generic slowness regarding the
> > > applications, go set up some profiles focusing on long running
> > > transactions.
> > >
> > > Generally, locks are always needed in a database system when concurrent
> > > users are assessing (in different ways) the same resource. MSSQL
> > > handles locks internally, and my view is not to mess with them. Of
> > > course you can use there are certain guidelines for the DBAs and
> > > developers to utilise locks more properly as scarce resource.
> > >
> > > Oracle guys might laugh over us on this one since in Oracle locks are
> > > not scarce resource.
> > >
> > > Hope this can help.
> > >
> > > Cheers,
> > >
> > > Yak