Thursday, March 8, 2012

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.
>
>

No comments:

Post a Comment