BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Tuesday, March 20, 2012
BOL says you should not kill a SELECT statement. Why not?
BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>
Sunday, March 11, 2012
Blocking Question
I am beginning to see blocked and blocking processes in
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ' If not how can I resolve the blocking problems '
Database is a highly transactional ~500 user database.
Thanks for any feedback.Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
--
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
> > I am beginning to see blocked and blocking processes in
> > Lock / Process ID tab under the current activity window.
> > SQL Server resolves this after a while but is this
> > normal ' If not how can I resolve the blocking problems '
> >
> > Database is a highly transactional ~500 user database.
> >
> > Thanks for any feedback.
>|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.com/sf_block_prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.com/reducing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Thanks to All.........
>--Original Message--
>I am beginning to see blocked and blocking processes in
>Lock / Process ID tab under the current activity window.
>SQL Server resolves this after a while but is this
>normal ' If not how can I resolve the blocking
problems '
>Database is a highly transactional ~500 user database.
>Thanks for any feedback.
>.
>
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ' If not how can I resolve the blocking problems '
Database is a highly transactional ~500 user database.
Thanks for any feedback.Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
--
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
> > I am beginning to see blocked and blocking processes in
> > Lock / Process ID tab under the current activity window.
> > SQL Server resolves this after a while but is this
> > normal ' If not how can I resolve the blocking problems '
> >
> > Database is a highly transactional ~500 user database.
> >
> > Thanks for any feedback.
>|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.com/sf_block_prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.com/reducing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Thanks to All.........
>--Original Message--
>I am beginning to see blocked and blocking processes in
>Lock / Process ID tab under the current activity window.
>SQL Server resolves this after a while but is this
>normal ' If not how can I resolve the blocking
problems '
>Database is a highly transactional ~500 user database.
>Thanks for any feedback.
>.
>
Blocking Question
I am beginning to see blocked and blocking processes in
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ? If not how can I resolve the blocking problems ?
Database is a highly transactional ~500 user database.
Thanks for any feedback.
Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.
|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
>
|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.co...prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.co...cing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ? If not how can I resolve the blocking problems ?
Database is a highly transactional ~500 user database.
Thanks for any feedback.
Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.
|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
>
|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.co...prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.co...cing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.
Blocking processes
In sql2005 database it happenings that 2 processes lock different tables and
each process wait for the other to finish - or something similar.
The result is that application waits and nothing works.
I heard that SQL2005 automatically handles this situations and kill process
who did less work. Is there some setting?
Regards,SAre you describing a deadlock scenario? SQL Server has had deadlock detectio
n and resolution since
version 1.0 (although the internal algorithms has changed over the versions)
. I suggest that you
start reading up on "deadlock" (Books Online, Google etc) to see if this is
what you refer to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
> In sql2005 database it happenings that 2 processes lock different tables a
nd each process wait for
> the other to finish - or something similar.
> The result is that application waits and nothing works.
> I heard that SQL2005 automatically handles this situations and kill proces
s who did less work. Is
> there some setting?
> Regards,S
>|||Well I have locked processes, which never finishes.
But as I heared, the SQL2005 now has automatic detection and kill the
process who has done less job until deadlock.
What can I do?
It's not mine application so I don't know exactly what is happening.
When I kill process manually, than everything works.
It's happening about once a w
.
Regards,Simon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Are you describing a deadlock scenario? SQL Server has had deadlock
> detection and resolution since version 1.0 (although the internal
> algorithms has changed over the versions). I suggest that you start
> reading up on "deadlock" (Books Online, Google etc) to see if this is what
> you refer to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
>|||> Well I have locked processes, which never finishes.
Than you don't have a deadlock, you just have a blocking scenario. Unless yo
u have a deadlock that
haven't been detected by SQL Server, which would be considered a bug. If it
is a blocking scenario,
you have to find who is blocking the others, and why that transaction doesn'
t finish.
> It's not mine application so I don't know exactly what is happening.
Blocking and deadlock problems is an application problem, so you need to tal
k to the application
vendor about this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:OO$MWAYWGHA.4212@.TK2MSFTNGP02.phx.gbl...
> Well I have locked processes, which never finishes.
> But as I heared, the SQL2005 now has automatic detection and kill the proc
ess who has done less
> job until deadlock.
> What can I do?
> It's not mine application so I don't know exactly what is happening.
> When I kill process manually, than everything works.
> It's happening about once a w.
> Regards,Simon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>
each process wait for the other to finish - or something similar.
The result is that application waits and nothing works.
I heard that SQL2005 automatically handles this situations and kill process
who did less work. Is there some setting?
Regards,SAre you describing a deadlock scenario? SQL Server has had deadlock detectio
n and resolution since
version 1.0 (although the internal algorithms has changed over the versions)
. I suggest that you
start reading up on "deadlock" (Books Online, Google etc) to see if this is
what you refer to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
> In sql2005 database it happenings that 2 processes lock different tables a
nd each process wait for
> the other to finish - or something similar.
> The result is that application waits and nothing works.
> I heard that SQL2005 automatically handles this situations and kill proces
s who did less work. Is
> there some setting?
> Regards,S
>|||Well I have locked processes, which never finishes.
But as I heared, the SQL2005 now has automatic detection and kill the
process who has done less job until deadlock.
What can I do?
It's not mine application so I don't know exactly what is happening.
When I kill process manually, than everything works.
It's happening about once a w
.Regards,Simon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Are you describing a deadlock scenario? SQL Server has had deadlock
> detection and resolution since version 1.0 (although the internal
> algorithms has changed over the versions). I suggest that you start
> reading up on "deadlock" (Books Online, Google etc) to see if this is what
> you refer to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
>|||> Well I have locked processes, which never finishes.
Than you don't have a deadlock, you just have a blocking scenario. Unless yo
u have a deadlock that
haven't been detected by SQL Server, which would be considered a bug. If it
is a blocking scenario,
you have to find who is blocking the others, and why that transaction doesn'
t finish.
> It's not mine application so I don't know exactly what is happening.
Blocking and deadlock problems is an application problem, so you need to tal
k to the application
vendor about this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:OO$MWAYWGHA.4212@.TK2MSFTNGP02.phx.gbl...
> Well I have locked processes, which never finishes.
> But as I heared, the SQL2005 now has automatic detection and kill the proc
ess who has done less
> job until deadlock.
> What can I do?
> It's not mine application so I don't know exactly what is happening.
> When I kill process manually, than everything works.
> It's happening about once a w
.> Regards,Simon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>
Blocking Processes
I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else.
When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
TIA,
Darwin
The following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:
>I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
>TIA,
>Darwin
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else.
When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
TIA,
Darwin
The following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:
>I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
>TIA,
>Darwin
Blocking Processes
I need some help with a problem that is growing worse. We are experiencing
a problem with our database where it stops responding due to a process that
is blocking other processes from executing. We had thought these were deadl
ocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute
is a select statement. I don't understand how a select statement can contr
ibute to a deadlock. So that has me wondering now if the problem is not a d
eadlock but something else.
When EM shows a process is blocking other processes, what is that problem an
d how do I solve it? This is getting serious for us. Any help would be gre
atly appreciated.
TIA,
DarwinThe following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:
>I need some help with a problem that is growing worse. We are experiencing a probl
em with our database where it stops responding due to a process that is blocking oth
er processes from executing. We had thought these were deadlocks but we could see t
hat
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execut
e is a select statement. I don't understand how a select statement can cont
ribute to a deadlock. So that has me wondering now if the problem is not a
deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I s
olve it? This is getting serious for us. Any help would be greatly appreciated.kred">
>TIA,
>Darwin
a problem with our database where it stops responding due to a process that
is blocking other processes from executing. We had thought these were deadl
ocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute
is a select statement. I don't understand how a select statement can contr
ibute to a deadlock. So that has me wondering now if the problem is not a d
eadlock but something else.
When EM shows a process is blocking other processes, what is that problem an
d how do I solve it? This is getting serious for us. Any help would be gre
atly appreciated.
TIA,
DarwinThe following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:
>I need some help with a problem that is growing worse. We are experiencing a probl
em with our database where it stops responding due to a process that is blocking oth
er processes from executing. We had thought these were deadlocks but we could see t
hat
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execut
e is a select statement. I don't understand how a select statement can cont
ribute to a deadlock. So that has me wondering now if the problem is not a
deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I s
olve it? This is getting serious for us. Any help would be greatly appreciated.kred">
>TIA,
>Darwin
Blocking process
I just found an interesting thing in a database.
I have a lot of locks and all locked processes are locked by SPID -2. The
information I get in Enterprise manager and Query analyzer is that the
proceses are blocked by -2.
I can't find any information of what SPID -2 is. The only thing I see is
when I run sp_lock, then I get a list with over 3000 rows of locks which are
held by -2.
Does anybody know what a negatvie SPID is and what I can do to resolve this?If you run a sp_who '-2'
What does this bring back?
Dylan kruger
"Jaana" <jaana.lehtonen@.banverket.se> wrote in message
news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> I just found an interesting thing in a database.
> I have a lot of locks and all locked processes are locked by SPID -2. The
> information I get in Enterprise manager and Query analyzer is that the
> proceses are blocked by -2.
> I can't find any information of what SPID -2 is. The only thing I see is
> when I run sp_lock, then I get a list with over 3000 rows of locks which
are
> held by -2.
> Does anybody know what a negatvie SPID is and what I can do to resolve
this?
>|||sp_who -2 gives the result The login '-2' does not exist.
"Dylan Kruger[MS]" <carmellobear1@.hotmail.com> skrev i meddelandet
news:OhcrsG8kDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If you run a sp_who '-2'
> What does this bring back?
> Dylan kruger
> "Jaana" <jaana.lehtonen@.banverket.se> wrote in message
> news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > I just found an interesting thing in a database.
> > I have a lot of locks and all locked processes are locked by SPID -2.
The
> > information I get in Enterprise manager and Query analyzer is that the
> > proceses are blocked by -2.
> > I can't find any information of what SPID -2 is. The only thing I see is
> > when I run sp_lock, then I get a list with over 3000 rows of locks which
> are
> > held by -2.
> > Does anybody know what a negatvie SPID is and what I can do to resolve
> this?
> >
> >
>
I have a lot of locks and all locked processes are locked by SPID -2. The
information I get in Enterprise manager and Query analyzer is that the
proceses are blocked by -2.
I can't find any information of what SPID -2 is. The only thing I see is
when I run sp_lock, then I get a list with over 3000 rows of locks which are
held by -2.
Does anybody know what a negatvie SPID is and what I can do to resolve this?If you run a sp_who '-2'
What does this bring back?
Dylan kruger
"Jaana" <jaana.lehtonen@.banverket.se> wrote in message
news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> I just found an interesting thing in a database.
> I have a lot of locks and all locked processes are locked by SPID -2. The
> information I get in Enterprise manager and Query analyzer is that the
> proceses are blocked by -2.
> I can't find any information of what SPID -2 is. The only thing I see is
> when I run sp_lock, then I get a list with over 3000 rows of locks which
are
> held by -2.
> Does anybody know what a negatvie SPID is and what I can do to resolve
this?
>|||sp_who -2 gives the result The login '-2' does not exist.
"Dylan Kruger[MS]" <carmellobear1@.hotmail.com> skrev i meddelandet
news:OhcrsG8kDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If you run a sp_who '-2'
> What does this bring back?
> Dylan kruger
> "Jaana" <jaana.lehtonen@.banverket.se> wrote in message
> news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > I just found an interesting thing in a database.
> > I have a lot of locks and all locked processes are locked by SPID -2.
The
> > information I get in Enterprise manager and Query analyzer is that the
> > proceses are blocked by -2.
> > I can't find any information of what SPID -2 is. The only thing I see is
> > when I run sp_lock, then I get a list with over 3000 rows of locks which
> are
> > held by -2.
> > Does anybody know what a negatvie SPID is and what I can do to resolve
> this?
> >
> >
>
blocking issue.
I have "scenario 2" blocking issue. the process is sleeping and open transac
tions is 1 or more.
However the command that was run was a prodedure that has "No Begin Transact
ion, or rollback or commit."
Any ideas?
I don't understand how the open transactions can be greater then zero.You are probably have SET IMPLICIT_TRANSACTIONS ON .
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:5F627C7E-59AD-4232-B80D-588E58B932C0@.microsoft.com...
> I have "scenario 2" blocking issue. the process is sleeping and open
transactions is 1 or more.
> However the command that was run was a prodedure that has "No Begin
Transaction, or rollback or commit."
> Any ideas?
> I don't understand how the open transactions can be greater then zero.|||I have not. Perhapes the default mode has been set in some configuration at
the server level. Is this possible?|||Yes, the default for a lot of drivers is to set implicit transactions on.
You can run profiler and make sure to include the Existing connections event
to see what gets set.
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A558E87B-FA4C-4FE4-B4D1-15D745B50F17@.microsoft.com...
> I have not. Perhapes the default mode has been set in some configuration
at the server level. Is this possible?
tions is 1 or more.
However the command that was run was a prodedure that has "No Begin Transact
ion, or rollback or commit."
Any ideas?
I don't understand how the open transactions can be greater then zero.You are probably have SET IMPLICIT_TRANSACTIONS ON .
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:5F627C7E-59AD-4232-B80D-588E58B932C0@.microsoft.com...
> I have "scenario 2" blocking issue. the process is sleeping and open
transactions is 1 or more.
> However the command that was run was a prodedure that has "No Begin
Transaction, or rollback or commit."
> Any ideas?
> I don't understand how the open transactions can be greater then zero.|||I have not. Perhapes the default mode has been set in some configuration at
the server level. Is this possible?|||Yes, the default for a lot of drivers is to set implicit transactions on.
You can run profiler and make sure to include the Existing connections event
to see what gets set.
Andrew J. Kelly SQL MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A558E87B-FA4C-4FE4-B4D1-15D745B50F17@.microsoft.com...
> I have not. Perhapes the default mode has been set in some configuration
at the server level. Is this possible?
Blocking issue
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
Blocking issue
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry :)SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry :)|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)
I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry :)SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
--
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry :)|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry :)
Thursday, March 8, 2012
Blocking
Hi everybody
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
...
...
Blkby: 80
I would appreciate any advice.
Harold
It depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
...
...
Blkby: 80
I would appreciate any advice.
Harold
It depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
Blocking
Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?
KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?
|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
processes?
KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?
|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Blocking
Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.
I second this question! Anyone have an easy solution for this?
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>
|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.
I second this question! Anyone have an easy solution for this?
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>
|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>
Blocking
Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Blocking
Hi everybody
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
..
..
Blkby: 80
I would appreciate any advice.
HaroldIt depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
..
..
Blkby: 80
I would appreciate any advice.
HaroldIt depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
Blocking
Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>
>
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>
>
Wednesday, March 7, 2012
Blocking
Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Blocking
Hi everybody
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
..
..
Blkby: 80
I would appreciate any advice.
HaroldIt depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
..
..
Blkby: 80
I would appreciate any advice.
HaroldIt depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
Blocking
Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>> Is there a way to setup SQL server to automatically kill a
>> blocking process after a certain time frame (after maybe 5
>> Minutes for example)?
>> We are running SQL Server 2000 service pack 3a.
>
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>> Is there a way to setup SQL server to automatically kill a
>> blocking process after a certain time frame (after maybe 5
>> Minutes for example)?
>> We are running SQL Server 2000 service pack 3a.
>
Subscribe to:
Posts (Atom)