Locks and "forever runnable" processes

[Yannick Turgeon]

Hello all,

I'm using SS 2000 and NT4 (and Access97 as front-end on another server)

Well, probably by lack of knowledge about table locks, I don't really know
where to start to present this problem. In Enterprise manager, section
"Management->Current activity->Locks/Objects", we have a couple (5-7) of
"forever runnable" processes, all related to two specific situations. Each
of them are for "SELECT" statements. It's been a long time since it's like
that. I've always been curious about them but the weren't causing any
problem. Now, after a modification, a third situation happened ("SELECT"
again)... and sometime a lock created by this new "forever runnable" process
blocks other functions that use the same table. All my table are linked with
an ODBC link.

Any help or suggestion where to search would be appreciated.

Thanks.

Yannick

[Erland Sommarskog]

Re: Locks and "foreve r runnable" processes

Yannick Turgeon (nobody@nowhere .com) writes:[color=blue]
> I'm using SS 2000 and NT4 (and Access97 as front-end on another server)
>
> Well, probably by lack of knowledge about table locks, I don't really
> know where to start to present this problem. In Enterprise manager,
> section "Management->Current activity->Locks/Objects", we have a couple
> (5-7) of "forever runnable" processes, all related to two specific
> situations. Each of them are for "SELECT" statements. It's been a long
> time since it's like that. I've always been curious about them but the
> weren't causing any problem. Now, after a modification, a third
> situation happened ("SELECT" again)... and sometime a lock created by
> this new "forever runnable" process blocks other functions that use the
> same table. All my table are linked with an ODBC link.
>
> Any help or suggestion where to search would be appreciated.[/color]

You could use DBCC INPUTBUFFER on the spids on the offending processes
to see exactly which SELECT statements they are running. You could also
use my aba_lockinfo collects a lot of information, including DBCC
INPUTBUFFER. It's at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

The reasons that SELECT runs "forever" is most often a bad query plan.
However, there is also a the possibility that a client does not get
the entire result set from SQL Server, and I've see reports that this
can happen when Access fills combo boxes, and there are many rows. (For
one poster, there appeared to be a limit between 450 and 500+ rows.)
--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp