|
|
You're in the
sql server connect
group:tracking SPID back to the user or client
sql server connect:
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 [quoted text, click to view] "Therat" <Therat@discussions.microsoft.com> wrote in message news:4B0BDD66-DA02-4AF5-9410-98E1F06E3581@microsoft.com... > 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 >
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
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. [quoted text, click to view] "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@discussions.microsoft.com> wrote in message > news:4B0BDD66-DA02-4AF5-9410-98E1F06E3581@microsoft.com... > > 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 > > > >
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 [quoted text, click to view] "Therat" <Therat@discussions.microsoft.com> wrote in message news:E6183442-5BC3-47E3-8120-9F4EC8A42FB2@microsoft.com... > 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@discussions.microsoft.com> wrote in message >> news:4B0BDD66-DA02-4AF5-9410-98E1F06E3581@microsoft.com... >> > 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 >> > >> >> >>
The application guy doesn't have a clue then as to what his application is
doing. You can run a trace and at some point you should see a cursor prepare that has the actual sql statement that the cursor is using. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Therat" <Therat@discussions.microsoft.com> wrote in message news:E6183442-5BC3-47E3-8120-9F4EC8A42FB2@microsoft.com... > 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@discussions.microsoft.com> wrote in message >> news:4B0BDD66-DA02-4AF5-9410-98E1F06E3581@microsoft.com... >> > 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 >> > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |