Replication failed

Hi,
We are using transactional replication and it failed and
showing following error
Error:
The process could not bulk copy into table 'TableName'.
Error Number:
22001
Category:ODBC
Source: ODBC
Full Message:
String data, right truncation
Is any one having solution for this?
Regards,
Manoj
Manoj,
can you check if the schemas are identical on publisher and subscriber if
you are doing a nosync initialization.
If you are sending over the table schema, can you ensure that you take the
collation.
Regards,
Paul Ibison

Replication errors - vicious circle

When I try to create a transactional publication, when I select the table I
want to replicate, I get the error "This table cannot be published because it
does not have a primary key column. Primary key columns are required for all
tables in transactional publications".
Then When I try to make the necessary changes to the table, such as adding a
column for the primary key, I get the error "Unable to modify table. Cannot
drop the table "tablename" because it is being used for replication."
I have never successfully replicated this table or any other. I can find no
evidence of any publications to remove.
I'm stuck!
Just answered my own question after reading thru a ton of posts on this forum.
"run sp_removedbreplication on each database..."
Thanks Paul Ibison!
"TahoePete" wrote:

> When I try to create a transactional publication, when I select the table I
> want to replicate, I get the error "This table cannot be published because it
> does not have a primary key column. Primary key columns are required for all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding a
> column for the primary key, I get the error "Unable to modify table. Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find no
> evidence of any publications to remove.
> I'm stuck!
>
|||Issue the following statements
sp_MSunmarkreplinfo 'ProblemTableName'
Then you need to update the colstat column
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns
SET colstat = colstat & ~4096
WHERE colstat & 4096 <>0
go
sp_configure 'allow updates', 2
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
> When I try to create a transactional publication, when I select the table
> I
> want to replicate, I get the error "This table cannot be published because
> it
> does not have a primary key column. Primary key columns are required for
> all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding
> a
> column for the primary key, I get the error "Unable to modify table.
> Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find
> no
> evidence of any publications to remove.
> I'm stuck!
>
|||I receive the error "ad hoc updates to system catalogues are not allowed"
from the update command.
"Hilary Cotter" wrote:

> Issue the following statements
> sp_MSunmarkreplinfo 'ProblemTableName'
> Then you need to update the colstat column
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE syscolumns
> SET colstat = colstat & ~4096
> WHERE colstat & 4096 <>0
> go
> sp_configure 'allow updates', 2
> go
> reconfigure with override
> go
>
> --
> 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
> "TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
> news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
>
>
|||I'm not sure what is wrong here - this command should enable this
sp_configure 'allow updates', 1
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:793A5914-F3E9-4195-9F70-BA7C544E4427@.microsoft.com...[vbcol=seagreen]
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
> "Hilary Cotter" wrote:

Replication Error...

I have a SQL server which has the databases installed with transactional replciation enabled. I got an error which is mentioned below in my distribution agent history :-

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.
(Source: FLORA\INSTANCE1 (Data source); Error number: 2627)
--------------------------

Can anyone let me know the reason for the above mentioned error?

Thanks,
Santhosh.C.Your subscriber is not in sync with your publisher. If the database is not too big or you can shut down the app, go ahead and reinitialize the subscription to sync the replication.

replication error with oracle

Hi,
I have a transactional replication with Oracle Server, and when it generate the initial snapshot, I got an error, saying ORA-00906, missing left parenthesis. It's actually just because when the snapshot agent tried to genenrate a primary key index, it has
a word of "CLUSTERED" in the script. Is there a way that I can modify the script or at least get rid of the "CLUSTERED' word?
Thanks
Hong Wang,
have a look at sp_addarticle. It has a @.creation_script parameter that
perhaps you could use to apply your own script.
HTH,
Paul Ibison
|||right click on your publication, select properties, go to the articles tab,
click on the browse button to the right of your table. in the snapshot tab,
uncheck include DRI.
Regenerate your snapshot and try again.
Thanks to Allan Mitchell (http://www.sqldts.com,
http://www.intmalemodel.com) for this solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eghZmwnXEHA.3944@.tk2msftngp13.phx.gbl...
> Hong Wang,
> have a look at sp_addarticle. It has a @.creation_script parameter that
> perhaps you could use to apply your own script.
> HTH,
> Paul Ibison
>

replication error while copy data

Hi,
I have a transactional replication between SQL Server and Oracle. We already have the schema exist in both databases. So the the snapshut I check the 'If the table exists, leave it unchanged' box. But when the data start to load for the intial snapshut, I
got an error saying' The process encountered invalid column data in bcp file ...' for an existing table. How can me trace down which column has error? What is the data type for the Rowguid in my subscriber ( I do not have a rowguid column setup in the su
bscriber)?
Thanks
Hong,
was this error encountered when creating the snapshot file or when
distributing it to Oracle?
You can get some more (hopefully useful) info by logging:
http://support.microsoft.com/?id=312292
HTH,
Paul Ibison
|||It is encountered when distributing it to Oracle.
Hong Wang
"Paul Ibison" wrote:

> Hong,
> was this error encountered when creating the snapshot file or when
> distributing it to Oracle?
> You can get some more (hopefully useful) info by logging:
> http://support.microsoft.com/?id=312292
> HTH,
> Paul Ibison
>
>
|||OK, then try the logging I mentioned but I had hoped that it was a snapshot
agent error, as this is almost certainly an Oracle issue - mapping of
datatypes etc. Anyway, let's see if the logging returns anything.
Regards,
Paul Ibison
sql

Replication Error while Configuring Distributor

Hi,
I am trying to setup transactional replication on a database within 1
sql instance. When I try to configure the pulisher/distributor, it errors out
with this message.
"SQL Server Enterprise Manager could not configure 'DZDEV007' as the
distributor for 'DZDEV007'.
Error 14234 : The specified '@.server' in invalid (valid values are returned
by sp_helpserver"
What's going on ? Please help.
TIA
Mohan
Hi Mohan,
The following KB seems to match most of your symptoms although your server
name appear to be too short for the problem occur:
http://support.microsoft.com/default...b;en-us;310639
You may also want to consider some other possibilities such as subtle
comparison issues related to the collation that you are using for your server.
-Raymond
"MO" wrote:

> Hi,
> I am trying to setup transactional replication on a database within 1
> sql instance. When I try to configure the pulisher/distributor, it errors out
> with this message.
> "SQL Server Enterprise Manager could not configure 'DZDEV007' as the
> distributor for 'DZDEV007'.
> Error 14234 : The specified '@.server' in invalid (valid values are returned
> by sp_helpserver"
> What's going on ? Please help.
> TIA
> Mohan

Replication Error after Upgrade (7.0-2000)

Scenario: 3 servers - one Subscriber, one Distributor, one Publisher. Transactional replication; immediate updating subscribers disallowed. Each article in its own publication... independent agents.
Subscriber & Publisher = SQL 7.0
Distributor recently upgraded to 2000, SP3a.
Issue: new publications created at publisher fail to initialize snapshot agent properly. Error: "Another snapshot agent for the publication is running". Agent then fails after total retries (default is 10... does not matter what I set it to).
All existing publications work fine; all existing subscriptions work fine.
So what's the deal?
It appears that once you upgrade the Distributor(s), you have to upgrade the Publisher(s) immediately thereafter if you wish to create new publications. I have not seen that documented however... anyone know?
Thanks!
X
This is documented somewhat. Check out
http://msdn.microsoft.com/library/de...grade_45ir.asp
It doesn't say do this immediately though.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Scenario: 3 servers - one Subscriber, one Distributor, one Publisher.
Transactional replication; immediate updating subscribers disallowed. Each
article in its own publication... independent agents.
> Subscriber & Publisher = SQL 7.0
> Distributor recently upgraded to 2000, SP3a.
> Issue: new publications created at publisher fail to initialize snapshot
agent properly. Error: "Another snapshot agent for the publication is
running". Agent then fails after total retries (default is 10... does not
matter what I set it to).
> All existing publications work fine; all existing subscriptions work fine.
> So what's the deal?
> It appears that once you upgrade the Distributor(s), you have to upgrade
the Publisher(s) immediately thereafter if you wish to create new
publications. I have not seen that documented however... anyone know?
> Thanks!
> X
|||Thanks for the response Hilary.
I read that article; it doesn't specify that publishers MUST be upgraded to 2000 once the Distributor has been upgraded. That's the issue here: we have an upgraded Distributor, but we cannot upgrade the Publisher for some time. In the meantime, we need to
create new publications. We are unable to do so however, due to the error I outlined. It APPEARS that once you upgrade your Distributor, you have to upgrade your Publisher before you can create new publications. That doesn't seem right, but I have yet to
find evidence of its validity.
FWIW: one of the DBAs here reported the same error after applying SPs/hotfixes on the Distributor w/o applying same to Publisher.
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||FYI: the following URL shows the configuration I outlined as a 'valid replication topology' http://msdn.microsoft.com/library/de...limpl_4joy.asp
However, it doe not indicate that new publications created on the 7.0 Publisher w/ 2k Distributor and 7.0 Subscriber(s) will fail. On the contrary - it leads one to believe that the new publications will be in 70 compatability mode, as the replication top
ology defaults to the lowest level in a mixed environment.
Odd then, that I get this error. (btw, we have recycled the servers)
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||Let me try to repro this tonight.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> Thanks for the response Hilary.
> I read that article; it doesn't specify that publishers MUST be upgraded
to 2000 once the Distributor has been upgraded. That's the issue here: we
have an upgraded Distributor, but we cannot upgrade the Publisher for some
time. In the meantime, we need to create new publications. We are unable to
do so however, due to the error I outlined. It APPEARS that once you upgrade
your Distributor, you have to upgrade your Publisher before you can create
new publications. That doesn't seem right, but I have yet to find evidence
of its validity.
> FWIW: one of the DBAs here reported the same error after applying
SPs/hotfixes on the Distributor w/o applying same to Publisher.[vbcol=seagreen]
> "Hilary Cotter" wrote:
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Each[vbcol=seagreen]
snapshot[vbcol=seagreen]
not[vbcol=seagreen]
fine.[vbcol=seagreen]
upgrade[vbcol=seagreen]
|||Were you able to reproduce the error?
Many thanks!!
X
"Hilary Cotter" wrote:

> Let me try to repro this tonight.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> to 2000 once the Distributor has been upgraded. That's the issue here: we
> have an upgraded Distributor, but we cannot upgrade the Publisher for some
> time. In the meantime, we need to create new publications. We are unable to
> do so however, due to the error I outlined. It APPEARS that once you upgrade
> your Distributor, you have to upgrade your Publisher before you can create
> new publications. That doesn't seem right, but I have yet to find evidence
> of its validity.
> SPs/hotfixes on the Distributor w/o applying same to Publisher.
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> Each
> snapshot
> not
> fine.
> upgrade
>
>
|||No, I was unable to get the error. What error did you get, and what service
packs were you running?
Where you using an Administrative Link password?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:C359EA3E-0ED4-417C-B1A9-F4F015253BD0@.microsoft.com...[vbcol=seagreen]
> Were you able to reproduce the error?
> Many thanks!!
> X
>
> "Hilary Cotter" wrote:
upgraded[vbcol=seagreen]
we[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
upgrade[vbcol=seagreen]
create[vbcol=seagreen]
evidence[vbcol=seagreen]
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Publisher.[vbcol=seagreen]
disallowed.[vbcol=seagreen]
is[vbcol=seagreen]
does[vbcol=seagreen]
work[vbcol=seagreen]
know?[vbcol=seagreen]
sql

replication error

Hi,
I have configured transactional replication with one publisher and one subscriber.
after completion of schema and bcp operation , it has given message as "posting commands into the distribution database" after that timedout expired .
Now the error message is
"The concurrent snapshot for publication 'Siebel OLTP Transactional' has not been activated by the log reader agent" and schema in shared folder got deleted and snopshat agent started again.
Pls provide resolution
Thanks
Murali
Murali,
you could try increasing the -QUERYTIMEOUT parameter of the distribution
agent's profile. The second error might be resulting from the first, but
just in case it isn't I'd increase the -QUERYTIMEOUT of the logreader also.
I'm not too sure where your post title fits in - "Space is not available in
TempDB" - did you also see this error message? Obviously in this case TempDB
needs increasing in size.
HTH,
Paul Ibison
|||I'd try setting the QueryTimeout parameter in the Snapshot agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tDHzD3YEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Murali,
> you could try increasing the -QUERYTIMEOUT parameter of the distribution
> agent's profile. The second error might be resulting from the first, but
> just in case it isn't I'd increase the -QUERYTIMEOUT of the logreader
also.
> I'm not too sure where your post title fits in - "Space is not available
in
> TempDB" - did you also see this error message? Obviously in this case
TempDB
> needs increasing in size.
> HTH,
> Paul Ibison
>

Replication down Log Shrinking

Hi,

Is there any relation between replication and the transaction log, we have set up transactional replication between our databases, and I had to clear the log as its size was huge, I also reduced the temp db size. After that, the replication is down, with an error, the subscriptions are invalid, reconfigure the Topology.

Is this related to the shrinking of the logs ?

These two things (shrinking the log, and the expiration of subscription) are not related.|||Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?|||

. Transaction log and Transactional replication very much related.

Transaction Log Space

For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances.

Ref :

http://msdn2.microsoft.com/en-us/library/aa237475(SQL.80).aspx

http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Madhu

|||

see the first thing i want to mention here is you should never ever trucate the TL of a replicated DB. Each subscription has a lif which is by default (336 hrs ) if i remember correctly, which can be changed in the property of of a publication. If you restart server the replication will not effect. it will re-start from where it left.

Madhu

|||

Why do you need to reduce the size of tempdb?

DO you have any free disk space issues?

I wouldn't suggest to shrink the TEMPDB and keep up the log size with regular transactionlog backups, as you have more information about Trans.log & Trans.replication relation.

Rick305652 wrote:

Ok Sounds logical, but when we have to reduce the size of tempdb, the way we do it is restart the sql server right ? In this case P2P configured has to go down. If yes, then what must be the ideal way of reducing the tempdb size on a system where P2P replication is configured ?

replication distribution server agents keep time out

Hi,
We have a distribution server that have all of our
transactional replications on it. All the distribution
agents and log reader agents keep time out and we have to
keep restart the agents.
What's the roob cause of this? Too many replications on
one distribution box? Or too many agents running at the
same time schedule?
Many thanks.
JJDon't the distribution agents keep trying for a certain number of times
before they error out? While they keep trying, you don't need to manually
restart them.
Distribution agents often times out in our environment. Causes I have found
are:
1. Blocking on the subscriber;
2. No more space on the subscriber database;
3. Server connection problem;
Richard
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:01a501c39284$479d5ee0$a401280a@.phx.gbl...
> Hi,
> We have a distribution server that have all of our
> transactional replications on it. All the distribution
> agents and log reader agents keep time out and we have to
> keep restart the agents.
> What's the roob cause of this? Too many replications on
> one distribution box? Or too many agents running at the
> same time schedule?
> Many thanks.
> JJ

Replication distribution cleanup error

I used the SQL Server 2005 publication wizard to set up transactional replication. It scheduled a job called "Distribution clean up: distribution" that calls sp_MSdistribution_cleanup. The job fails every time it runs. The xp_cmdshell is enabled and SQL Server Agent runs under an account that has sufficient privileges to execute it. The account also has full rights to the repldata subdirectories. I have applied service pack 1.

I looked through the sp_MSdistribution_cleanup code and it looks like there are two stored procedures and one function missing from the SQL server databases. They are

master.dbo.sp_MSunc_to_drive sys.fn_escapecmdshellsymbolsremovequotes sys.sp_MSdrop_distribution_agentid

Where can I get a copy of these stored procedures? The expired distribution data is slowly consuming my remaining hard drive space. Thanks.

Joe

Hi, Joe,

There seems to be a bug in our code where we should not call master.dbo.sp_MSunc_to_drive, instead we should call sys.sp_MSunc_to_drive. In SQL Server 2005, we have move most of the system sps to mssqlsystemresource database, and some of them are hidden from end users as they're not supposed to be used directly by customers. sys.sp_MSunc_to_drive, sys.fn_escapecmdshellsymbolsremovequotes and sys.sp_MSdrop_distribution_agentid are hidden sps/function thus you don't see them but they do exist.

I will do more research and see if there is a workaround.

By the way, can you post the exact error message you're seeing from distribution cleanup job (by using replication monitor to view the job history)? Are you using dynamic snapshot merge replication? Any specific information about your replication setup would be helpful.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for answering me so quickly. The problem has been resolved. The account under which xp_cmdshell was executing did not have the proper access to the repldata share.

Joe

|||

Glad to hear that the problem is solved. The issue mentioned in my post is a minor issue and should not cause any functional problems. It would be helpful for other visitors of this thread if you can post the error message you're seeing before it's fixed (it should still be available in replication agent history).

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I'm really sorry, but the clean up agent already deleted the history. I think part of the message was "Could not clean up the distribution transaction tables."

Joe

sql

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cpu.
Is there a profile setting that I'm missing?
Thanks all in advance!!
Message posted via http://www.droptable.com
On Mar 1, 1:39 pm, "fnadal via droptable.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over a
> million cpu and continued to grow until I stopped and restarted the the pull
> subscription agent on the distribution server. Then it went down to about a
> hundred cpu. As I'm writing this article its already up to three thousand cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.droptable.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?
|||The cpu usage. When I do a select spid,loginame,program_name, cpu from master.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million for
that agent.
Tracy McKibben wrote:
>[quoted text clipped - 9 lines]
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
Message posted via http://www.droptable.com
|||That is a cumulative value since the connection started, not an immediate
usage value.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
> --
> Message posted via http://www.droptable.com
>
|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:[vbcol=seagreen]
>That is a cumulative value since the connection started, not an immediate
>usage value.
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cp
u.
Is there a profile setting that I'm missing?
Thanks all in advance!!
Message posted via http://www.droptable.comOn Mar 1, 1:39 pm, "fnadal via droptable.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over
a
> million cpu and continued to grow until I stopped and restarted the the pu
ll
> subscription agent on the distribution server. Then it went down to about
a
> hundred cpu. As I'm writing this article its already up to three thousand
cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.droptable.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?|||The cpu usage. When I do a select spid,loginame,program_name, cpu from maste
r.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million fo
r
that agent.
Tracy McKibben wrote:
>[quoted text clipped - 9 lines]
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
Message posted via http://www.droptable.com|||That is a cumulative value since the connection started, not an immediate
usage value.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
> --
> Message posted via http://www.droptable.com
>|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:[vbcol=seagreen]
>That is a cumulative value since the connection started, not an immediate
>usage value.
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1sql

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cpu.
Is there a profile setting that I'm missing?
Thanks all in advance!!
--
Message posted via http://www.sqlmonster.comOn Mar 1, 1:39 pm, "fnadal via SQLMonster.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over a
> million cpu and continued to grow until I stopped and restarted the the pull
> subscription agent on the distribution server. Then it went down to about a
> hundred cpu. As I'm writing this article its already up to three thousand cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.sqlmonster.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?|||The cpu usage. When I do a select spid,loginame,program_name, cpu from master.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million for
that agent.
Tracy McKibben wrote:
>> We have transactional replication set up. The agent is running on the
>> subscriber. However the CPU continues to grow on the subscriber. Yesterday
>[quoted text clipped - 9 lines]
>> --
>> Message posted viahttp://www.sqlmonster.com
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
--
Message posted via http://www.sqlmonster.com|||That is a cumulative value since the connection started, not an immediate
usage value.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
>> We have transactional replication set up. The agent is running on the
>> subscriber. However the CPU continues to grow on the subscriber.
>> Yesterday
>>[quoted text clipped - 9 lines]
>> --
>> Message posted viahttp://www.sqlmonster.com
>>Can you explain a little more clearly? Exactly what statistic are you
>>looking at? What value is growing?
> --
> Message posted via http://www.sqlmonster.com
>|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:
>That is a cumulative value since the connection started, not an immediate
>usage value.
>> The cpu usage. When I do a select spid,loginame,program_name, cpu from
>> master.
>[quoted text clipped - 12 lines]
>>Can you explain a little more clearly? Exactly what statistic are you
>>looking at? What value is growing?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Replication Deadlock

Dear All,
I'm just configured transactional replication.
I have 2 DB Servers, OLTP Server and Reporting Server.
I uses transactional replication to replicate the data from OLTP Server
to Reporting Server.
I configured the replcation to use Push Subcription.
I set the schedule every 3 minutes bcos I need the report to be avaiable
as soon as possible.
Unfortunately, my Reporting Server got deadlock bcos of it.
Did I configure incorrectly?
Pls I need some suggestion for it.
Thanks
Robert Lie
Robert,
perhaps you could use the NOLOCK hint on the reporting server queries.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication conflict tables

Greetings!
We have been running transactional replication environment in development and QA test. The other evening in a "emergency", development requested several table changes to many of the published tables. I was out of the office and with no laptop available,
I tried to blindly guide a coworker through the process of reestablishing the articles and the subscription (not entirely sure of what he had removed). Apparently, we erred and he published and subscribed everything, but now we have a conflict_ table fo
r every single table in the publication. None of the tables have any rows. The published and subscriber database are both on the same server (as is the distribution database).
I can remove the conflict tables for the subscriber database, but they also exist in the published database as system tables which can't be dropped. I haven't run across an sp that can remove these and am thinking I won't be able to short of rebuilding t
he database. Might've SQL Server gotten the idea that this was to be merge replication? That seems like the only reason I got these. Is there any clean way to remove these conflict tables without rebuilding the database?
Thanks much in advance!
Steve
It sounds like he created a merge publication and then deleted it. You can
safely drop these conflict tables (as long as you aren't running any merge,
immediate updating or queued updating subscribers) by runing the following
command in QA
select 'drop table '+name from sysobjects where name like 'conflict_%'
Then in your results pane select the results and then paste it back in the
Query pane, and exectue it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve H" <shoerle@.charter.net> wrote in message
news:19042C30-8B0A-41D3-AD44-4DE62725C3C2@.microsoft.com...
> Greetings!
> We have been running transactional replication environment in development
and QA test. The other evening in a "emergency", development requested
several table changes to many of the published tables. I was out of the
office and with no laptop available, I tried to blindly guide a coworker
through the process of reestablishing the articles and the subscription (not
entirely sure of what he had removed). Apparently, we erred and he
published and subscribed everything, but now we have a conflict_ table for
every single table in the publication. None of the tables have any rows.
The published and subscriber database are both on the same server (as is the
distribution database).
> I can remove the conflict tables for the subscriber database, but they
also exist in the published database as system tables which can't be
dropped. I haven't run across an sp that can remove these and am thinking I
won't be able to short of rebuilding the database. Might've SQL Server
gotten the idea that this was to be merge replication? That seems like the
only reason I got these. Is there any clean way to remove these conflict
tables without rebuilding the database?
> Thanks much in advance!
> Steve

Replication complete event on publisher

I would like to setup transactional replication so that when the publisher
finishes sending data out, a .NET 2.0 app is run. Is there an event I can
grab onto, or would I have to set a trigger up on a system table?
Add it to the final job step of your merge or distribution agent.
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
"Rono" <Rono@.discussions.microsoft.com> wrote in message
news:E31B4A0E-4F1A-420C-AADC-AC39EB76C738@.microsoft.com...
>I would like to setup transactional replication so that when the publisher
> finishes sending data out, a .NET 2.0 app is run. Is there an event I can
> grab onto, or would I have to set a trigger up on a system table?
|||In your distribution agent job there are 3 steps. The second one - "Run
agent" does the real work. In the Advanced tab there is workflow defined so
that when this step completes, the job quits, while if it fails, the job
goes to the next step. So, you'll need another step to be added to call your
app, and ensure that the workflow is such that the step 4 is arrived at on
success of step 2.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication Cleanup in high volume database

We're having intermittent performance problems on a SQL 2005 SP1 system that
we suspect is related to transactional replication. There may be overlap
with Jack Griffith's thread "Replication system disk performance severly
degrades after 1 month", but I'm not sure.....
While troubleshooting the problem, we attempted to make things better by
clearing out any transactions that had already been replicated to the
subscriber (EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 0). BAD, BAD, BAD!!!!! Setting @.max_distretention = 0
marked our subscription as inactive and we had to rebuild the subscriber
database to get it back in sync with the publisher.
I estimate add/update 2-4 million rows a day in our publication database.
The distribution database has grown to 9GB in < a week.
I have not altered any of the database cleanup jobs as of yet. The current
Distribution Cleanup job runs every 10 min and is taking 6-15 minutes to run.
We observe a tremendous number of disk reads on the Distribution database
disks. When we experience performance issues, we also see a HUGE number of
memory pages lasting ~ 15 seconds and occuring exactly every 2 minutes.
Restarting the SQLServer service seems to clear up the performance issues for
several hours.
* msrepl_commands rowcount approaches 35 million rows and
msrepl_transactions approaches 6 million rows. With a high volume in
transactional replication, should there be any indexes on the tables?
* While tracing through the MS SPs, I found that sp_MSdelete_dodelete only
deletes the top 5000 rows from MSrepl_transactions. In a high volume
transactional state, this might not keep up. Can this be changed safely?
* What other parameters can be changed to keep the transactions only as long
as they are needed but yet not mark the subscription as inactive?
Thanks!
If you have a few subscribers use raid 10, if you have a large number use
raid 5.
Set max_distretention to 3 days. This will only pool undistributed commands
for up to 3 days.
the setting for only deleting 5000 rows at a time is by design. If you
choose to modify it to something higher make sure you make the modification
to the end of
sp_MSdelete_publisherdb_trans as well.
The appropriate indexes are in place on msrepl_commands and
msrepl_transactions. You might want to defrag these indexes to see if that
helps or update statistics.
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
"RL" <RL@.discussions.microsoft.com> wrote in message
news:71F2956A-5B80-4918-993F-F8CF3AD2C0D5@.microsoft.com...
> We're having intermittent performance problems on a SQL 2005 SP1 system
> that
> we suspect is related to transactional replication. There may be overlap
> with Jack Griffith's thread "Replication system disk performance severly
> degrades after 1 month", but I'm not sure.....
> While troubleshooting the problem, we attempted to make things better by
> clearing out any transactions that had already been replicated to the
> subscriber (EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 0). BAD, BAD, BAD!!!!! Setting @.max_distretention =
> 0
> marked our subscription as inactive and we had to rebuild the subscriber
> database to get it back in sync with the publisher.
> I estimate add/update 2-4 million rows a day in our publication database.
> The distribution database has grown to 9GB in < a week.
> I have not altered any of the database cleanup jobs as of yet. The
> current
> Distribution Cleanup job runs every 10 min and is taking 6-15 minutes to
> run.
> We observe a tremendous number of disk reads on the Distribution database
> disks. When we experience performance issues, we also see a HUGE number
> of
> memory pages lasting ~ 15 seconds and occuring exactly every 2 minutes.
> Restarting the SQLServer service seems to clear up the performance issues
> for
> several hours.
>
> * msrepl_commands rowcount approaches 35 million rows and
> msrepl_transactions approaches 6 million rows. With a high volume in
> transactional replication, should there be any indexes on the tables?
> * While tracing through the MS SPs, I found that sp_MSdelete_dodelete only
> deletes the top 5000 rows from MSrepl_transactions. In a high volume
> transactional state, this might not keep up. Can this be changed safely?
> * What other parameters can be changed to keep the transactions only as
> long
> as they are needed but yet not mark the subscription as inactive?
> Thanks!
|||Thanks Hilary. We have one subscriber and are using RAID 10 as recommended.
The max_distretention is set to 72 hours already. The distribution database
was just rebuilt a week ago, so it only has 4 days of deletes in it, so i'm
not sure an index defrag will help.
Why do we need to keep 3 days worth of commands that have already been
replicated? Any suggestions on how to make the distribution cleanup job run
more efficiently? It's running at least 50% of the time now. Is it possible
we're just outpacing what replication was designed for? In my stress testing
before moving to SQL 2005 x64, replication was the weak performance link.
Thanks,
Rob
"Hilary Cotter" wrote:

> If you have a few subscribers use raid 10, if you have a large number use
> raid 5.
> Set max_distretention to 3 days. This will only pool undistributed commands
> for up to 3 days.
> the setting for only deleting 5000 rows at a time is by design. If you
> choose to modify it to something higher make sure you make the modification
> to the end of
> sp_MSdelete_publisherdb_trans as well.
> The appropriate indexes are in place on msrepl_commands and
> msrepl_transactions. You might want to defrag these indexes to see if that
> helps or update statistics.
>
> --
> 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
> "RL" <RL@.discussions.microsoft.com> wrote in message
> news:71F2956A-5B80-4918-993F-F8CF3AD2C0D5@.microsoft.com...
>
>
|||I like to keep 3 days around in case I leave on a Friday night of a long
weekend when I come back on Tuesday I can still restart failed distribution
agents without them going expired on me.
The problem is that if you delete too many rows at a time the locks will
escalate to table level locks and your log reader agents will fail.
I have a friend who was in a similar situation as yours (Wachovia bank IIRC
in NYC) and he manually deleted rows in large batches. Reverse engineer to
the procs to see what to delete and stop your log reader agents while doing
this.
I also worked on a similar problem in Philly where the backlog was something
similar and we were able to get through everything in 2 hours.
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
"RL" <RL@.discussions.microsoft.com> wrote in message
news:1F28BAE1-FD52-470F-9B35-E596A2544D46@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary. We have one subscriber and are using RAID 10 as
> recommended.
> The max_distretention is set to 72 hours already. The distribution
> database
> was just rebuilt a week ago, so it only has 4 days of deletes in it, so
> i'm
> not sure an index defrag will help.
> Why do we need to keep 3 days worth of commands that have already been
> replicated? Any suggestions on how to make the distribution cleanup job
> run
> more efficiently? It's running at least 50% of the time now. Is it
> possible
> we're just outpacing what replication was designed for? In my stress
> testing
> before moving to SQL 2005 x64, replication was the weak performance link.
> Thanks,
> Rob
> "Hilary Cotter" wrote:
sql

Replication Between Instances Issue.

I'm doing Transactional Replication from Server1 to PC1.

Server1 Info:
Domain: A
SQL Server 2000 Enterprise Edition SP3.
SQL Server Services under Local System Account.

PC1 Info:
Domain: B
SQL Server 2000 Developer Edition SP3
SQL Server Services under Local System Account.

The process I did in EM was:

1. Create Remote Server to PC1 from Server1. Did not check RPC checkbox.Map all remote logins to same name. Did not check password.
2. Created Publisher and Distributer on Server1.
3. Publish new publication of DB1 in Server1.
4. Push new subscriber , auto create DB1_copy in PC1.
5. Snapshot successful.
6. Distributor Agent Failed.

Error Message:
The process could not connect to Subscriber 'Pc1'.

Error Details:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
(Source: PC1 (Data source); Error number: 18452)

I have identical local WINNT ID's and password on both machine. I was guessing that would solve it. But it didn't.

Previously I did snapshot and transactional replication on the same machine itself( Server1 only). It was succesful.

Did I miss out anything?
Has it got to do with any Remote Server setting?
I've heard that Replication can only be done on the same domain. Is this true?
What if there are cases of different domains? Or in a workgroup perharps?
Or can it be overwritten by having 2 identical local WINNT ids in both machines?
Lastly, Can Personal Edition SQL Server 2000 do replication?Help?|||By not knowing your environment completely, I would suggest that you change your sql startup account from local system account to a network account. Use the same network account for both machine and, of course, it has to be admin in both machine.

Good luck!|||I've actually solve the problem and concluded by myself that replication can be done on separate domains or workgroups or whatever.
As long as an IP can ping the other IP, it will work.

During registration of Publisher and Subcriber, check "show advanced options" during the wizard. From there, there will be a section where u can click to chose Windows Authetication or SQL Server Login.

Chose SQL Server Login during Publisher Wizard and also SQL Server Login during Subscriber Wizard. And voila!, that will solve the problem.

However, again, this is thru test and fail method of solving a problem, are there any pointer anybody would like to share?sql

Replication between instances

we are looking to doing a transactional replication of a database of 12 gig
between instances of sql 2000 on the same server.
We want to do this so we can have a read database and a write database.
On other setups we have had a write database and a read database different
servers. We have never attempted to do it on the same server. Has anyone
found any issues doing this in a live enviroument.
Cheers
In general transactional replication (which should should be using) has a
low resource footprint on your server. However this depends highly on the
amount of transactions you are replicating.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Rory P" <Rory P@.discussions.microsoft.com> wrote in message
news:7B06EFE4-5EF9-4450-A375-BB87598EC20D@.microsoft.com...
> we are looking to doing a transactional replication of a database of 12
> gig
> between instances of sql 2000 on the same server.
> We want to do this so we can have a read database and a write database.
> On other setups we have had a write database and a read database different
> servers. We have never attempted to do it on the same server. Has anyone
> found any issues doing this in a live enviroument.
> Cheers
|||On 2004-12-03, Rory P <> wrote:
> we are looking to doing a transactional replication of a database of 12 gig
> between instances of sql 2000 on the same server.
> We want to do this so we can have a read database and a write database.
> On other setups we have had a write database and a read database different
> servers. We have never attempted to do it on the same server. Has anyone
> found any issues doing this in a live enviroument.
Never tried with transactional, but merge works. Publisher and subscriber
can even be same server (same instanc), you publish one database and
subscribe another one, on same instance.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr