Re: tracking SPID back to the user or client
- From: "Arnie Rowland" <arnie@xxxxxxxx>
- Date: Tue, 22 Aug 2006 13:00:38 -0700
That is one of the major reasons that applications should not be using
CURSORs.
During the entire time that the cursor is active, it is setting locks on the
data. Those locks are what are causing the blocking behavior.
The solution is to revisit the code and create set operations instead of
using CURSORS.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Therat" <Therat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E6183442-5BC3-47E3-8120-9F4EC8A42FB2@xxxxxxxxxxxxxxxx
I'f been there, tried to get what's wrong. The only thing i can see is
that
the user has a cursorfetch. So the next thing I could do is trying to ask
the
user what's he/she is doing. The application guy just tells my company
that
it is the server that does this. Problem is the believe it, strange. The
server has been configured that sql can only use 1 processor. just in
case.
The user must have something on its screen and I would like to know what.
If wred the articals you provided.
"Andrew J. Kelly" wrote:
I don't know how to trace it back to a specific user but I doubt that
will
solve your problem. If that user is running the same app as everyone else
the blocker will most likely change each time. It is the app that you
need
to address not the user. You can run trace to see what commands the user
has
issued to get you into that spot or maybe simply using DBCC
INPUTBUFFER(spid) to see what their last command was. Then look at the
app
or database code to see why it is blocking and fix the source.
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
--
Andrew J. Kelly SQL MVP
"Therat" <Therat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B0BDD66-DA02-4AF5-9410-98E1F06E3581@xxxxxxxxxxxxxxxx
Here's a problem that I can't fix. I have an application that uses a
SQL
database. The connection is made through ODBC. Also this application
uses
the
same username for all the connections; yes it's one of those.
Now this application generates a blocking in witch one spid is in the
head
of the chain.
Looking in the Enterprise manager --> current Activity --> lock /
process
ID
I see the blocking process.
Looking in the Process Info at this spid I only see the default name
(the
same for every user). The hostname is empty and the network address is
the
mac address of the router. So left with nothing to trace it back to the
real
user.
I scanned through the system tables for a clue who's responsible for
this
block
With netstat I see pc's connected to the database server.
Mssql knows were the data has to go but why can't I find out what pc is
connected to a spid????? To trace the blocking spid back to the real
user.
Who can make my happy again
.
- References:
- Re: tracking SPID back to the user or client
- From: Andrew J. Kelly
- Re: tracking SPID back to the user or client
- Prev by Date: Re: in sql server 2000 when you issue a alter table to add new column, can you specifiy the order? meaning i want that column to be between existing columns, not at the last
- Next by Date: Re: in sql server 2000 when you issue a alter table to add new column, can you specifiy the order? meaning i want that column to be between existing columns, not at the last
- Previous by thread: Re: tracking SPID back to the user or client
- Next by thread: Re: tracking SPID back to the user or client
- Index(es):
Relevant Pages
|
