SQL Server 2005 Replication CPU 100%

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

batballs2002@hotmail.com - 29 Jan 2007 12:45 GMT

We have recently installed a new SQL Server 2005 box to compliment our
existing SQL Server 2000. To ensure data consisitentcy we have
employed replication between the two servers. We are using
transactional replication, with the replication being applied at
scheduled times during the day. The problem seems to be a short while
after the replication has completed (up to 30 mins) the publisher /
distributor server (SQL Server 2005) cpu maxes out at 100%. This can
only be recovered by a server re-boot.

Can anyone shed any light / thoughts on this area.

Many thanks in advance

Mark

Reply to this Message

Hilary Cotter - 29 Jan 2007 15:08 GMT

I've seen this. First off 100% cpu utilization means your processor is very
busy which is not a bad thing per se. Use task manager to discover what
component on your server is consuming the most amount of CPU. If it is the
log reader you may be bumping into a problem where the article cache gets
locked. Please refer to
http://support.microsoft.com/kb/812263/en-us

I still see something like this on SQL 2005, especially when on my laptop
when I go back and forth between hibernations and suspensions. I take it
your production environment is not running on laptops right;)?

One option is to kill the log reader agent and then to restart it.

Signature

Hilary Cotter

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

> We have recently installed a new SQL Server 2005 box to compliment our
> existing SQL Server 2000. To ensure data consisitentcy we have
[quoted text clipped - 10 lines]
>
> Mark

Reply to this Message

batballs2002@hotmail.com - 29 Jan 2007 15:22 GMT

Thanks for this response Hilary.

First of we are not using laptops in our production environment!
Which is a good thing!!

The kb article does seem to be very close to our issue, and it may
point us to a log reader issue, which I will investigate further. One
publication certainly drops columns when it replicates!

The main issue when the server goes though is actually communicating
with sql server itself, I cannot connect to it and issue any commands,
i.e. sp_who2 to find out what processes are running. Are there any
other ways to kill the log agent reader and re-start?

Mark

> I've seen this. First off 100% cpu utilization means your processor is very
> busy which is not a bad thing per se. Use task manager to discover what
[quoted text clipped - 31 lines]
>
> > Mark

Reply to this Message