I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
Jay
Jay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay
|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
David Gugick
Imceda Software
www.imceda.com
|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> not
>
>
|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going[vbcol=seagreen]
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
means[vbcol=seagreen]
that[vbcol=seagreen]
thought[vbcol=seagreen]
that[vbcol=seagreen]
|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> WHERE
> that
> going
> means
> that
> thought
> that
>
Showing posts with label caused. Show all posts
Showing posts with label caused. Show all posts
Thursday, March 8, 2012
blocking caused from SQL with (nolock) hint
I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>
Blocking caused by Replication
Hi All,
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
SQL Server DBA
Does this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA
|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug?
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>
|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.
|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>
|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.
|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
SQL Server DBA
Does this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA
|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug?
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>
|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.
|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>
|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.
|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking caused by Replication
Hi All,
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on th
e
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is th
e
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on th
e
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is th
e
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
>
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking caused by Replication
Hi All,
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
--
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> > Hi All,
> >
> > I wonder how I can pursue a blocking problem caused by SQL Replication.
> >
> > We are having a performance issue on one of our busy sql server 2000
> > (build
> > 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> > the
> > same box.
> > We have seen this problem after we moved sql server to a new box. SW and
> > HW
> > configuration are the same.
> >
> > There are two head blockers.
> >
> > Waittype is PAGELATCH_SH
> > Inputbuffer is sp_MSget_repl_commands
> >
> > CPU is %100 and LogReader is always running for hours until we stop it.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Ada
> > --
> > SQL Server DBA
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
--
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >We have seen this problem after we moved sql server to a new box. SW and HW
> >configuration are the same.
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
--
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >
> >Removing replication and setting up again resolves the problem temporarily.
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
I wonder how I can pursue a blocking problem caused by SQL Replication.
We are having a performance issue on one of our busy sql server 2000 (build
818) machines. There is tran.repl. Publisher db and subscriber dbs are on the
same box.
We have seen this problem after we moved sql server to a new box. SW and HW
configuration are the same.
There are two head blockers.
Waittype is PAGELATCH_SH
Inputbuffer is sp_MSget_repl_commands
CPU is %100 and LogReader is always running for hours until we stop it.
Any idea?
Thanks,
Ada
--
SQL Server DBADoes this apply?
http://support.microsoft.com/kb/812263/en-us
Also what happens when you stop the log reader agent and restart it? Is the
condition cleared?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> Hi All,
> I wonder how I can pursue a blocking problem caused by SQL Replication.
> We are having a performance issue on one of our busy sql server 2000
> (build
> 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> the
> same box.
> We have seen this problem after we moved sql server to a new box. SW and
> HW
> configuration are the same.
> There are two head blockers.
> Waittype is PAGELATCH_SH
> Inputbuffer is sp_MSget_repl_commands
> CPU is %100 and LogReader is always running for hours until we stop it.
> Any idea?
> Thanks,
> Ada
> --
> SQL Server DBA|||No, it does not apply.
The log reader was unresponsive/hung. Resetting up repl cleared the problem.
I wonder if it might be a sql bug'
Thanks for your response.
--
SQL Server DBA
"Hilary Cotter" wrote:
> Does this apply?
> http://support.microsoft.com/kb/812263/en-us
> Also what happens when you stop the log reader agent and restart it? Is the
> condition cleared?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:F913F6BA-56EB-4CE6-A972-089334B84479@.microsoft.com...
> > Hi All,
> >
> > I wonder how I can pursue a blocking problem caused by SQL Replication.
> >
> > We are having a performance issue on one of our busy sql server 2000
> > (build
> > 818) machines. There is tran.repl. Publisher db and subscriber dbs are on
> > the
> > same box.
> > We have seen this problem after we moved sql server to a new box. SW and
> > HW
> > configuration are the same.
> >
> > There are two head blockers.
> >
> > Waittype is PAGELATCH_SH
> > Inputbuffer is sp_MSget_repl_commands
> >
> > CPU is %100 and LogReader is always running for hours until we stop it.
> >
> > Any idea?
> >
> > Thanks,
> >
> > Ada
> > --
> > SQL Server DBA
>
>|||On Tue, 19 Sep 2006 01:45:01 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>We have seen this problem after we moved sql server to a new box. SW and HW
>configuration are the same.
No idea, but under the hit-it-with-a-stick school of repair, have you
tried removing replication and then restarting it?
J.|||Removing replication and setting up again resolves the problem temporarily.
--
SQL Server DBA
"JXStern" wrote:
> On Tue, 19 Sep 2006 01:45:01 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >We have seen this problem after we moved sql server to a new box. SW and HW
> >configuration are the same.
> No idea, but under the hit-it-with-a-stick school of repair, have you
> tried removing replication and then restarting it?
> J.
>|||On Wed, 20 Sep 2006 18:24:02 -0700, Ada
<Ada@.discussions.microsoft.com> wrote:
>Removing replication and setting up again resolves the problem temporarily.
Er, ... what does sp_who2 show as the head of the blocking chain?
Don't think I ever have tried it with publisher and subscriber on the
same machine. This is one-way, right? If it's two-way, and you
forget to turn on the bit, things would get ugly, might be something
like this.
J.|||It's one way.
--
SQL Server DBA
"JXStern" wrote:
> On Wed, 20 Sep 2006 18:24:02 -0700, Ada
> <Ada@.discussions.microsoft.com> wrote:
> >
> >Removing replication and setting up again resolves the problem temporarily.
> Er, ... what does sp_who2 show as the head of the blocking chain?
> Don't think I ever have tried it with publisher and subscriber on the
> same machine. This is one-way, right? If it's two-way, and you
> forget to turn on the bit, things would get ugly, might be something
> like this.
> J.
>
>
Blocking by sp_sqlagent_log_jobhistory
Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
Arshad
arshadmd-nospam@.gmail.com
Arshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
Arshad
arshadmd-nospam@.gmail.com
Arshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
Blocking by sp_sqlagent_log_jobhistory
Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
Blocking by sp_sqlagent_log_jobhistory
Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?
Subscribe to:
Posts (Atom)