About once or twice a week, our SQL server stops responding. When this happens, we are not able to connect to it via EM or QA. The only solution at this point is to restart the SQL Service. The SQL errorlog always shows the following type of error after t
his happens:
Process ID 72:167 owns resources that are blocking processes on Scheduler 3.
I am finding it hard to troubleshoot this because there is essentially no way to find out what process is causing the blocking. I know what the SPID causing the blocking was, but due to the restart of SQLService, I don't know what it was actually doing.
Can anyone shed more light on this problem? The only potential solution I can think of is to run a trace and capture all the events before the block occurs so that we can identify the cause of the blocking...
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:A008E96F-84B5-4D89-935A-C6B0C6C7A113@.microsoft.com...
> About once or twice a week, our SQL server stops responding. When this
happens, we are not able to connect to it via EM or QA. The only solution at
this point is to restart the SQL Service. The SQL errorlog always shows the
following type of error after this happens:
> Process ID 72:167 owns resources that are blocking processes on Scheduler
3.
> I am finding it hard to troubleshoot this because there is essentially no
way to find out what process is causing the blocking. I know what the SPID
causing the blocking was, but due to the restart of SQLService, I don't know
what it was actually doing.
> Can anyone shed more light on this problem? The only potential solution I
can think of is to run a trace and capture all the events before the block
occurs so that we can identify the cause of the blocking...
>
I would suggest that you turn on the SQL Server Profiler and let it capture
the database activity. By doing so, you should be able to see what was
happening in the server when it finally locks up. That should give you
some direction to start in.
Rick Sawtell
MCT, MCSD, MCDBA
Showing posts with label responding. Show all posts
Showing posts with label responding. Show all posts
Sunday, March 11, 2012
blocking processes on scheduler
About once or twice a week, our SQL server stops responding. When this happe
ns, we are not able to connect to it via EM or QA. The only solution at this
point is to restart the SQL Service. The SQL errorlog always shows the foll
owing type of error after t
his happens:
Process ID 72:167 owns resources that are blocking processes on Scheduler 3.
I am finding it hard to troubleshoot this because there is essentially no wa
y to find out what process is causing the blocking. I know what the SPID cau
sing the blocking was, but due to the restart of SQLService, I don't know wh
at it was actually doing.
Can anyone shed more light on this problem? The only potential solution I ca
n think of is to run a trace and capture all the events before the block occ
urs so that we can identify the cause of the blocking..."DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:A008E96F-84B5-4D89-935A-C6B0C6C7A113@.microsoft.com...
> About once or twice a week, our SQL server stops responding. When this
happens, we are not able to connect to it via EM or QA. The only solution at
this point is to restart the SQL Service. The SQL errorlog always shows the
following type of error after this happens:
> Process ID 72:167 owns resources that are blocking processes on Scheduler
3.
> I am finding it hard to troubleshoot this because there is essentially no
way to find out what process is causing the blocking. I know what the SPID
causing the blocking was, but due to the restart of SQLService, I don't know
what it was actually doing.
> Can anyone shed more light on this problem? The only potential solution I
can think of is to run a trace and capture all the events before the block
occurs so that we can identify the cause of the blocking...
>
I would suggest that you turn on the SQL Server Profiler and let it capture
the database activity. By doing so, you should be able to see what was
happening in the server when it finally locks up. That should give you
some direction to start in.
Rick Sawtell
MCT, MCSD, MCDBA
ns, we are not able to connect to it via EM or QA. The only solution at this
point is to restart the SQL Service. The SQL errorlog always shows the foll
owing type of error after t
his happens:
Process ID 72:167 owns resources that are blocking processes on Scheduler 3.
I am finding it hard to troubleshoot this because there is essentially no wa
y to find out what process is causing the blocking. I know what the SPID cau
sing the blocking was, but due to the restart of SQLService, I don't know wh
at it was actually doing.
Can anyone shed more light on this problem? The only potential solution I ca
n think of is to run a trace and capture all the events before the block occ
urs so that we can identify the cause of the blocking..."DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:A008E96F-84B5-4D89-935A-C6B0C6C7A113@.microsoft.com...
> About once or twice a week, our SQL server stops responding. When this
happens, we are not able to connect to it via EM or QA. The only solution at
this point is to restart the SQL Service. The SQL errorlog always shows the
following type of error after this happens:
> Process ID 72:167 owns resources that are blocking processes on Scheduler
3.
> I am finding it hard to troubleshoot this because there is essentially no
way to find out what process is causing the blocking. I know what the SPID
causing the blocking was, but due to the restart of SQLService, I don't know
what it was actually doing.
> Can anyone shed more light on this problem? The only potential solution I
can think of is to run a trace and capture all the events before the block
occurs so that we can identify the cause of the blocking...
>
I would suggest that you turn on the SQL Server Profiler and let it capture
the database activity. By doing so, you should be able to see what was
happening in the server when it finally locks up. That should give you
some direction to start in.
Rick Sawtell
MCT, MCSD, MCDBA
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
Subscribe to:
Posts (Atom)