How to _really_ kill blocking spids?
in [Server Configuration]
|
From: Misty on 28 Jun 2010 16:22 Are you sure the connection is made and dropped? could it be that the table is linked in the app? MauryMarkowit wrote: Re: How to _really_ kill blocking spids? 01-May-07 "TheSQLGuru" wrote: Well it's hard to say. All I know for sure is that any access to the tables locks the subsequent users. Let me get specific; last night there was an access on a view that joins two tables, a list of securities and a list of owners. This access blocked, for whatever reason. From that point on everyone in the office was locked out of the securities table, any access would be blocked by that spid, and the securities table is used all over the place, so everyone came complaining. This really doesn't make any sense to me. In an attempt to avoid these in the future, is there a way to say that a join is "read only"? In this particular example the join to security was simply to retrieve the name, it is a non-updatable access. Maury Previous Posts In This Thread: On Monday, April 30, 2007 5:08 PM MauryMarkowit wrote: How to _really_ kill blocking spids? Every so often I get blocks on actions that simply should not cause blocks, like selecting a text field from 100 rows of a 1000 row table. This causes other users to be "locked out", and of course that must be *my* fault. So I go into enterprise manager, find the culperit, and Kill Process it, and... nothing. Ever. I have never once seen this kill the offending command. So then I figure out which user is causing it and ask them to kill off their (Access) app, and refresh. Now the blocking command is empty, but it *still* won't go away. No, this is not because of a rollback. These operations take tiny fractions of a second to run and are *read only*. Can anyone offer an explaination of what's happening, and how to REALLY force kill these things? Maury On Monday, April 30, 2007 11:01 PM TheSQLGuru wrote: Are you SURE these spids you are trying to kill are actually active, running Are you SURE these spids you are trying to kill are actually active, running spids and not sleeping ones? Are you seeing the last executed command for a spid that isn't really doing anything at the moment? That would explain why KILL doesn't do anything. -- TheSQLGuru President Indicium Resources, Inc. "Maury Markowitz" <MauryMarkowitz(a)discussions.microsoft.com> wrote in message news:C6EAD511-6AA2-4FF8-87AA-5716B18CF4D6(a)microsoft.com... On Tuesday, May 01, 2007 9:51 AM MauryMarkowit wrote: Re: How to _really_ kill blocking spids? "TheSQLGuru" wrote: Well it's hard to say. All I know for sure is that any access to the tables locks the subsequent users. Let me get specific; last night there was an access on a view that joins two tables, a list of securities and a list of owners. This access blocked, for whatever reason. From that point on everyone in the office was locked out of the securities table, any access would be blocked by that spid, and the securities table is used all over the place, so everyone came complaining. This really doesn't make any sense to me. In an attempt to avoid these in the future, is there a way to say that a join is "read only"? In this particular example the join to security was simply to retrieve the name, it is a non-updatable access. Maury On Tuesday, May 01, 2007 3:44 PM MauryMarkowit wrote: Re: How to _really_ kill blocking spids? "Aaron Kempf" wrote: I am! Maury Submitted via EggHeadCafe - Software Developer Portal of Choice Entity Framework 4.0 POCO-First development and POCO Template http://www.eggheadcafe.com/tutorials/aspnet/488279c4-214b-47ef-b152-955f3795ee13/entity-framework-40-pocofirst-development-and-poco-template.aspx
From: Dan on 29 Jun 2010 07:03 I think that's the old EggHead reply I've seen yet - over 3 years since the last post to the thread :P Dan "Misty Ellington" wrote in message news:2010628162151misty_ellington(a)yahoo.com... > Are you sure the connection is made and dropped? could it be that the > table is linked in the app? > > > > MauryMarkowit wrote: > > Re: How to _really_ kill blocking spids? > 01-May-07 > > "TheSQLGuru" wrote: > > > Well it's hard to say. All I know for sure is that any access to the > tables > locks the subsequent users. Let me get specific; last night there was an > access on a view that joins two tables, a list of securities and a list of > owners. This access blocked, for whatever reason. From that point on > everyone > in the office was locked out of the securities table, any access would be > blocked by that spid, and the securities table is used all over the place, > so > everyone came complaining. > > This really doesn't make any sense to me. > > In an attempt to avoid these in the future, is there a way to say that a > join is "read only"? In this particular example the join to security was > simply to retrieve the name, it is a non-updatable access. > > Maury > > Previous Posts In This Thread: > > On Monday, April 30, 2007 5:08 PM > MauryMarkowit wrote: > > How to _really_ kill blocking spids? > Every so often I get blocks on actions that simply should not cause > blocks, > like selecting a text field from 100 rows of a 1000 row table. This causes > other users to be "locked out", and of course that must be *my* fault. > > So I go into enterprise manager, find the culperit, and Kill Process it, > and... nothing. Ever. I have never once seen this kill the offending > command. > So then I figure out which user is causing it and ask them to kill off > their > (Access) app, and refresh. Now the blocking command is empty, but it > *still* > won't go away. > > No, this is not because of a rollback. These operations take tiny > fractions > of a second to run and are *read only*. > > Can anyone offer an explaination of what's happening, and how to REALLY > force kill these things? > > Maury > > On Monday, April 30, 2007 11:01 PM > TheSQLGuru wrote: > > Are you SURE these spids you are trying to kill are actually active, > running > Are you SURE these spids you are trying to kill are actually active, > running > spids and not sleeping ones? Are you seeing the last executed command for > a > spid that isn't really doing anything at the moment? That would explain > why > KILL doesn't do anything. > > -- > TheSQLGuru > President > Indicium Resources, Inc. > > "Maury Markowitz" <MauryMarkowitz(a)discussions.microsoft.com> wrote in > message news:C6EAD511-6AA2-4FF8-87AA-5716B18CF4D6(a)microsoft.com... > > On Tuesday, May 01, 2007 9:51 AM > MauryMarkowit wrote: > > Re: How to _really_ kill blocking spids? > "TheSQLGuru" wrote: > > > Well it's hard to say. All I know for sure is that any access to the > tables > locks the subsequent users. Let me get specific; last night there was an > access on a view that joins two tables, a list of securities and a list of > owners. This access blocked, for whatever reason. From that point on > everyone > in the office was locked out of the securities table, any access would be > blocked by that spid, and the securities table is used all over the place, > so > everyone came complaining. > > This really doesn't make any sense to me. > > In an attempt to avoid these in the future, is there a way to say that a > join is "read only"? In this particular example the join to security was > simply to retrieve the name, it is a non-updatable access. > > Maury > > On Tuesday, May 01, 2007 3:44 PM > MauryMarkowit wrote: > > Re: How to _really_ kill blocking spids? > "Aaron Kempf" wrote: > > > I am! > > Maury > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > Entity Framework 4.0 POCO-First development and POCO Template > http://www.eggheadcafe.com/tutorials/aspnet/488279c4-214b-47ef-b152-955f3795ee13/entity-framework-40-pocofirst-development-and-poco-template.aspx
|
Pages: 1 Prev: mirror error message - failover Next: Moving SSIS 2005 packages from test to prod. |