Merge Repliction - Run Stored Procedure when merge agent starts

I have database on SQL Server 2000 set up with a merge publication.
This publication is configured with a number of dynamic filters to
reduce the amount of data sent to each client. Each client has an
anonymous pull subscription. The merge process can be triggered by the
windows sync manager and my application.

To improve performance I have created some helper tables to hold the
mapping between user login and primary keys of selected entities.

For the replicated data to be correct the contents of the helper tables
needs to be up to date.

I need to fire off a stored procedure on the publisher before
replication starts to verify that this data is up to date. I can not
see any documented way of doing this however I have been experimenting
with some unorthodox systems.

Firstly has anyone any ideas?

I have been considering adding a trigger to some of the tables used by
the Microsoft replication code - yes I know this is very nasty.

My problems arise because executing this stored procedure will cause
some data to be updated. In updating data we could create a new
generation in the database. I must therefore run my stored procedure
before any the Microsoft code makes any generation checks / updates.

Anyone done anything similar, Anyone have any better ideas?

Any comments would be gratefully received.(tedd_n_alex@.yahoo.com) writes:
> My problems arise because executing this stored procedure will cause
> some data to be updated. In updating data we could create a new
> generation in the database. I must therefore run my stored procedure
> before any the Microsoft code makes any generation checks / updates.
> Anyone done anything similar, Anyone have any better ideas?

Not being very good at replication, I can only recommend you to visit
microsoft.public.sqlserver.replication. There are some guys that knows
replicataion really well there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Merge Replication with MSDE

I have two MSDE servers on different machines and am
trying to set up merge replication between the two. When
the merge agent runs on the publisher, it indicates that
the "process could not connect to the Subscriber" (SQL
Server does not exist or access denied).
I'm not sure if matters, but I'm running in a peer-to-
peer network. I have my SQL Agent service on the
publisher running under a local machine account, and that
account has access to the publisher, however I wasn't
sure how to grant access to that account on the
Subscriber, or if that was even necessary.
Any help would be appreciated.
- Jim T.
Jim
I am not positive, but I think that we ran into this problem as well. I
beleive that the problem is that the local machine account on the publisher
(or subscriber) can't authenticate with the other machine which means that
the SQL Server Agent can't connect to the other machine to make replication
happen. If I remember correctly, we had to have a non-local (domain)
account that each domain trusts and each machine allows access to the
database on. I'm not sure, but I think it may also be possible to have
local accounts as long as both the username and password are the same on
both machines. I'm pretty sure that there is a KB article titled somthing
like "How to run replication accross non-trusted domains" that may help. If
you need to change the SQL Agent's service acount, take a look at KB article
283811. Sorry to be so vague, but I hope it points you in the right
direction.
HTH
Ron L
"Jim Toth" <anonymous@.discussions.microsoft.com> wrote in message
news:14b301c4b3e6$5d41a650$a401280a@.phx.gbl...
>I have two MSDE servers on different machines and am
> trying to set up merge replication between the two. When
> the merge agent runs on the publisher, it indicates that
> the "process could not connect to the Subscriber" (SQL
> Server does not exist or access denied).
> I'm not sure if matters, but I'm running in a peer-to-
> peer network. I have my SQL Agent service on the
> publisher running under a local machine account, and that
> account has access to the publisher, however I wasn't
> sure how to grant access to that account on the
> Subscriber, or if that was even necessary.
> Any help would be appreciated.
> - Jim T.

Merge Replication Status

I'm using sp_replmonitorhelpmergesession to determine the status of
multiple push subscriptions.
When I set the Merge Agent to Slow Link, I get a status of 4 and the
message "No Data needed to be Merged" when there are no changes.
I'd like to get it to return the Status 4 when it's idle, but I want
to increase the polling interval from 60 to 180.
So I copy the Agent Profile "Slow Link" and alter the polling interval
and validation intervals only.
When I copy the slow Link Profile and use the copy, I get a Status of
3 and the message "Waiting xxx second(s) before polling for further
changes."
Where/how are these controlled?
pollinginterval
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
"Brian Bunin" <BBunin@.SBCGlobal.net> wrote in message
news:1176335136.270222.63650@.n59g2000hsh.googlegro ups.com...
> I'm using sp_replmonitorhelpmergesession to determine the status of
> multiple push subscriptions.
>
> When I set the Merge Agent to Slow Link, I get a status of 4 and the
> message "No Data needed to be Merged" when there are no changes.
> I'd like to get it to return the Status 4 when it's idle, but I want
> to increase the polling interval from 60 to 180.
> So I copy the Agent Profile "Slow Link" and alter the polling interval
> and validation intervals only.
> When I copy the slow Link Profile and use the copy, I get a Status of
> 3 and the message "Waiting xxx second(s) before polling for further
> changes."
> Where/how are these controlled?
>
|||On Apr 12, 8:48 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> pollinginterval
> --
> Hilary Cotter
>
Yes, but why does changing that alter the behavior of the Status and
messages?

Merge Replication SQL2000 Error 2812

Hi !

When trying to start a Merge Replication agent I get the following Error message:

The process could not enumerate the changes at the subscriber. 2812

The snapshot agent works fine as far as I can see.

The replication is set up between a Win2000 / SQL7 / SP 4 and a Win2003 / SQL2000 / SP 3a machine. Sqlserveragent on both machines is run as a system account.

Any tip is welcome!

Thanks
VincentJSProbably it was deadlock or merge agent cannot lock what does it need during enumerating. It is normal thing because of database activity. Try to repeat it and if problem still presents - check all processes by profiler and find where agent stops. Sometimes agent continue to work even if everything is red in EM.

Also you have to set up da omain account for sql server and an sql agent - I wonder how it is working at all (replication) under a local account.|||Hi snail!

I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.

It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)

With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:

Process could not enumerate changes at subscriber. Error 2812

Some additional information is that the merge agent stops with:

call sp_MSEnumerateChanges(?,?,?,?,?)

I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.

Any more suggestions?

Thanks a lot anyway for your help!

Greetings,
VincentJS|||Are you trying to replicate a publisher to a subscriber with different schema on both side?

Originally posted by VincentJS
Hi snail!

I understand what you told me and believe me, it (replication) works fine as a local system account or with a local administrator account.

It wokrs like a charm between the distributor/publisher (WIN2000/SQL7) and another server (subscriber) outside the DMZ (NT4/SQL7)

With the new subscriber (WIN2003/SQL2000) I keep getting the following error when starting the merge agent:

Process could not enumerate changes at subscriber. Error 2812

Some additional information is that the merge agent stops with:

call sp_MSEnumerateChanges(?,?,?,?,?)

I very recently installed SP 3a on the new subscriber (after reading on microsoft/technet) but that didn't help.

Any more suggestions?

Thanks a lot anyway for your help!

Greetings,
VincentJS|||Hello!

That's a good question. I've run and rerun so many tests (but always cleaning up afterwards with sp_mergesubscription_cleanup) that honestly at this stage i couldn't give you a clear answer on that point.

I've reinitialized the subscription a lot of times though and that didn't do it either.

I DID try to publish another table with merging (to another database) and that produced the same error type, despite the fact that I used a brandnew subscription with a totally different file.

Did this answer your question?

Thanks for your help!
VincentJs|||You mentioned about the brand new file, does that mean you have a different schema on the subscriber and trying to get replicated with the publisher with another schema and setup the merge without reinitialize the whole db.

I recalled I have the same problem before with that error message, what I did is to try to recreate my subscriber database and then leave it empty as it is until I create the snapshot to pull over to the subscriber by initiializing snapshot. Then it work properly.You can give a try but, please backup your subscriber database in case you need to restore back the database.|||Hi!

I wrote file but i should have written Table (what's in a name right?). Anyway, no i won't try what you suggested until only the very last moment when all else fails. At this moment its too risky and although i do have backups of all databases, i want to avoid "contaminating" or reinstallations as much as possible.

In the meantime i ran the merge agent in verbose-mode and i got the following messages (simplified):

REPLAGENT STATUS: 3
DistribServer.Pubdb: call {sp_MSenumcolums (?,?)}
SubscrServer.Subdb: call {sp_MSenumchanges (?,?,?,?,?)}
Percent complete: 0
The process could not enumerate changes at the subscriber
REPLAGENT STATUS: 6
Percent complete: 0
Category: COMMAND
Source: failed command
Number:
Message: call sp_MSenumChanges(?,?,?,?,?)

There's more of course but I think this covers the essence. Basically ALL the commands preceding the error ran normally... only at the (presumably) last step mentionned hereabove the merge agent fails.

Have you got any clue what's going wrong?

Thanks! I'll see you tomorrow i hope... It's quite late now here and i'm going home... I've got to eat and sleep sometimes :)

VincentJSsql

merge replication snapshot agent fails

I'm trying to setup merge replication and get the following error from
the snapshot agent after I've finished the 'New Publication Wizard'.
This doesn't make sense to me as it is an automatic process. Also, it
looks like it is complaining about a 'dbo.MSmerge_cont...' view which
is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
Command Text: IF
OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
VIEW
[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
e, SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
command, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
commandSetupDelegate, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
strQuery, SqlConnection connection)
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at
Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
103)
Get help: http://help/103
can you enable logging and post the log back here?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B312292&sd=tech
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
"gabe" <gschnaub@.gmail.com> wrote in message
news:1163095482.849374.43790@.m73g2000cwd.googlegro ups.com...
> I'm trying to setup merge replication and get the following error from
> the snapshot agent after I've finished the 'New Publication Wizard'.
> This doesn't make sense to me as it is an automatic process. Also, it
> looks like it is complaining about a 'dbo.MSmerge_cont...' view which
> is a view created automatically for the merge publication.
> ERROR MESSAGE:
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> FULL DETAILS:
> Message: The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> Command Text: IF
> OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
> VIEW
> [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
> Parameters:
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e, SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
> command, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
> commandSetupDelegate, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
> strQuery, SqlConnection connection)
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at
> Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
> MSSQLServer, Error number: 103)
> Get help: http://help/103
> Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
> 103)
> Get help: http://help/103
>

Merge replication problem

I am having a problem with sp_MSfetchidentityrange in a merge replication
with one subscriber.
The merge agent history shows the following 4 errors after executing the
last command: exec sp_MSfetchidentityrange N'Capacity_Type', 0
exec sp_MSadd_merge_history 32, 6, N'The merge process could not retrieve
identity range resource for table ''Capacity_Type''
and...
exec sp_MSadd_repl_error 2309, 0, 8, N'Merge Replication Provider',
'-2147200969', N'The merge process could not retrieve identity range resource
for table ''Capacity_Type''.'
and...
exec sp_MSadd_repl_error 2309, 0, 1, N'ABDN-SQL\OSLDB', '0', N'{call
sp_MSfetchidentityrange(?, 0) }'
and...
exec sp_MSadd_repl_error 2309, 0, 5, N'ABDN-SQL\OSLDB', '21195', N'A valid
identity range is not available. Check the data type of the identity column.'
The strange thing is that 12 other articles (before 'Capacity_Type) have
been replicated ok.
I.e. in a trace log you might see a run of successfull commands being
executed...
Step 1: exec sp_MSfetchidentityrange N'Thrusters', 0
Step 2: exec sp_MSfetchidentityrange N'Capacities', 0
Step 3: exec sp_MSfetchidentityrange N'Engines', 0
Step 4: exec sp_MSfetchidentityrange N'Towing', 0
Etc, etc
After I found this I thought I might try to remove the 'Capacity_type'
article from the publication.
I did this but then found the next article starting causing the exact same
errors as before.
E.g.
Step 2: exec sp_MSfetchidentityrange N'Area', 0
This leads me to believe it's not related specifically to any article.
Any help would be really appreciated as this is occuring on a production
server.
can you check for the existence of the following tables?
for merge -
distribution database on publisher - MSrepl_identity_range
publication and subscription database MSrepl_identity_range
for transactional -
distribution database on publisher - MSrepl_identity_range
publication database MSpub_identity_range
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
"Chris Winstone" <chris@.offshore-shipbrokers.co.uk(donotspam)> wrote in
message news:CDAB657D-303A-4F64-AAD8-CCAEFE523D37@.microsoft.com...
> I am having a problem with sp_MSfetchidentityrange in a merge replication
> with one subscriber.
> The merge agent history shows the following 4 errors after executing the
> last command: exec sp_MSfetchidentityrange N'Capacity_Type', 0
> exec sp_MSadd_merge_history 32, 6, N'The merge process could not retrieve
> identity range resource for table ''Capacity_Type''
> and...
> exec sp_MSadd_repl_error 2309, 0, 8, N'Merge Replication Provider',
> '-2147200969', N'The merge process could not retrieve identity range
resource
> for table ''Capacity_Type''.'
> and...
> exec sp_MSadd_repl_error 2309, 0, 1, N'ABDN-SQL\OSLDB', '0', N'{call
> sp_MSfetchidentityrange(?, 0) }'
> and...
> exec sp_MSadd_repl_error 2309, 0, 5, N'ABDN-SQL\OSLDB', '21195', N'A valid
> identity range is not available. Check the data type of the identity
column.'
> The strange thing is that 12 other articles (before 'Capacity_Type) have
> been replicated ok.
> I.e. in a trace log you might see a run of successfull commands being
> executed...
> Step 1: exec sp_MSfetchidentityrange N'Thrusters', 0
> Step 2: exec sp_MSfetchidentityrange N'Capacities', 0
> Step 3: exec sp_MSfetchidentityrange N'Engines', 0
> Step 4: exec sp_MSfetchidentityrange N'Towing', 0
> Etc, etc
> After I found this I thought I might try to remove the 'Capacity_type'
> article from the publication.
> I did this but then found the next article starting causing the exact same
> errors as before.
> E.g.
> Step 2: exec sp_MSfetchidentityrange N'Area', 0
> This leads me to believe it's not related specifically to any article.
> Any help would be really appreciated as this is occuring on a production
> server.
>
|||Hilary. Thanks for replying
There is MSrepl_identity_range on both the subscriber and publisher. There
is also a MSpub_identity_range table on the publisher. However I checked the
contents of all these tables and found that there was 82 records in
msrepl_identity_range on the publisher and only 3 records in
msrepl_identity_range on the subscriber. There are no records in
mspub_identity_range on the publisher.
Thanks
"Hilary Cotter" wrote:

> can you check for the existence of the following tables?
> for merge -
> distribution database on publisher - MSrepl_identity_range
> publication and subscription database MSrepl_identity_range
> for transactional -
> distribution database on publisher - MSrepl_identity_range
> publication database MSpub_identity_range
> --
> 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
> "Chris Winstone" <chris@.offshore-shipbrokers.co.uk(donotspam)> wrote in
> message news:CDAB657D-303A-4F64-AAD8-CCAEFE523D37@.microsoft.com...
> resource
> column.'
>
>
|||Hillary. I ended up removing replication and setting up my publications
again. But alas I'm having problems creating a publication from a script.
See later post.
Thanks anyway.
"Chris Winstone" <chris@.offshore-shipbrokers.co.uk(donotspam)> wrote in
message news:29CCA893-9E4B-4D9F-B18C-9210E8D910B5@.microsoft.com...[vbcol=seagreen]
> Hilary. Thanks for replying
> There is MSrepl_identity_range on both the subscriber and publisher. There
> is also a MSpub_identity_range table on the publisher. However I checked
> the
> contents of all these tables and found that there was 82 records in
> msrepl_identity_range on the publisher and only 3 records in
> msrepl_identity_range on the subscriber. There are no records in
> mspub_identity_range on the publisher.
> Thanks
> "Hilary Cotter" wrote:

merge replication metedata cleanup error

Hi,

We have a merge subscription which is reporting the following error at the subscriber:

The Merge Agent failed after detecting the retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet set to the Subscriber. You must reinitalize the subscription (without upload)

I'm a bit confused about this - there have been no changes made at the publisher - why would we be getting this message?

thanks
Bruce

The data changes that the merge agent is referring to are the metadata contained in the merge tables MSmerge_contents, MSmerge_tombstone, MSmerge_genhistory. Periodically this data needs to be purged otherwise it starts to impact performance.

If the retention period is passed the subscription will expire as the meta data will be removed. You will need to reinitialize the subscription. Here is some more information about setting the retention period.

http://msdn2.microsoft.com/en-us/library(d=robot)/ms151188.aspx

Thanks

Nabila Lacey

Merge Replication is very slow

Hi
We have configured merge replication in SQL 2005 (SP 1) for a database
having more than 100 GB. The merge agent duration was for every 5 mins.
Normally it used to take around 2 to 3 mins to complete. It was working fine
till now but now it is taking around 45 minutes. When checked and viewed
details in the replication monitor the below message was displayed for a long
time.
The process is waiting for a response from the query '{call
sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
Any help in this regard is appreciated
Thanks.
Are you using join Filters? Also you might want to drop your retention
period so that metadata is purged more frequently.
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
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
> Hi
> We have configured merge replication in SQL 2005 (SP 1) for a database
> having more than 100 GB. The merge agent duration was for every 5 mins.
> Normally it used to take around 2 to 3 mins to complete. It was working
> fine
> till now but now it is taking around 45 minutes. When checked and viewed
> details in the replication monitor the below message was displayed for a
> long
> time.
> The process is waiting for a response from the query '{call
> sys.sp_MSenumchangesdirect(?,?,?,?,?,?,?,?,90,1,0, '39B06489-B2C7-4FA4-B109-8609CD6C18FF')}'
> Any help in this regard is appreciated
> Thanks.
|||In addition to Hilary's suggestions, look at if you really need all the data
at the subscribers. Does all data change at both places, if not utilize the
downloadonly articles. And when using filters, see if you can use any of the
filtering enhancements in SQL Server 2005.
Also was there a lot of activity on the database recently that is causing
this wait?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
> Are you using join Filters? Also you might want to drop your retention
> period so that metadata is purged more frequently.
> --
> 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
>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:A96295C7-71C5-4764-9093-6737DED1E033@.microsoft.com...
>
|||Thanks for the reply
I don't have any join Filters.
The retention period was 14 days and now changed to 7 days. Removed some
records from MSmerge_contents &
MSmerge_genhistory.
Now there is some improvement. But still it is taking more than it used to.
Data changes are there at both (subscriber & publisher) ends. It is a 24/7
high activity database.
Checked memory & bandwidth , CPU, etc., all seems to be normal.
Thanks,
"Mahesh [MSFT]" wrote:

> In addition to Hilary's suggestions, look at if you really need all the data
> at the subscribers. Does all data change at both places, if not utilize the
> downloadonly articles. And when using filters, see if you can use any of the
> filtering enhancements in SQL Server 2005.
> Also was there a lot of activity on the database recently that is causing
> this wait?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ugqNAvvEHHA.3820@.TK2MSFTNGP02.phx.gbl...
>
>
|||If it is a highly active database, then it will be due to metadata growth.
If you can be sure that the subscriber will be synchronizing often, then you
can further reduce the retention. However remember that if the subscriber
fails to synchronize within the retention, it will expire and will need
reinitialization.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:9456F61D-74FD-45AF-BE89-8BFFEFD571DF@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply
> I don't have any join Filters.
> The retention period was 14 days and now changed to 7 days. Removed some
> records from MSmerge_contents &
> MSmerge_genhistory.
> Now there is some improvement. But still it is taking more than it used
> to.
> Data changes are there at both (subscriber & publisher) ends. It is a 24/7
> high activity database.
> Checked memory & bandwidth , CPU, etc., all seems to be normal.
> Thanks,
>
> "Mahesh [MSFT]" wrote:

Merge Replication error while applying Snapshot

Hi,

i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.

2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.

does the account under which the agent is running under have access to the share?|||

Greg,

Thanks for giving me the response...

Actually i am not clear about the Account... How to see the Account under which Agent is running?

I still dont understand where we are linking the Account and Agent.

Can you help on this ?

Thanks in advance.

|||

Greg,

Are you asking the about Agent in Central Server or in the Subscriber.

Thanks.

|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||

There are two places you may need to check.

1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.

2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.

Hope the above will be helpful.

Thanks

Yunjing

|||

Hi All,

Thanks for all you replies. I solved the problem i faced.

Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.

To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.

Thanks,

Thams.

Merge Replication error while applying Snapshot

Hi,

i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.

2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.

does the account under which the agent is running under have access to the share?|||

Greg,

Thanks for giving me the response...

Actually i am not clear about the Account... How to see the Account under which Agent is running?

I still dont understand where we are linking the Account and Agent.

Can you help on this ?

Thanks in advance.

|||

Greg,

Are you asking the about Agent in Central Server or in the Subscriber.

Thanks.

|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||

There are two places you may need to check.

1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.

2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.

Hope the above will be helpful.

Thanks

Yunjing

|||

Hi All,

Thanks for all you replies. I solved the problem i faced.

Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.

To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.

Thanks,

Thams.

Merge Replication Error

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR Pub-TRPSQL2-4 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I am getting the above error in Merge Replication. any one having any idea pls let me know.

Thanks & Regards,

Kasi.

Can you edit the merge agent job step by adding a couple of parameters?

-OutputVerboseLevel 2 -Output your-file-name

Then run the merge agent job again and look the error details in the output file.

Merge Replication Error

Replication-Replication Merge Subsystem: agent TRPSQL3-ThomsonResearch-TR Pub-TRPSQL2-4 failed. The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history log

I am getting the above error in Merge Replication. any one having any idea pls let me know.

Thanks & Regards,

Kasi.

Can you edit the merge agent job step by adding a couple of parameters?

-OutputVerboseLevel 2 -Output your-file-name

Then run the merge agent job again and look the error details in the output file.

Merge Replication between sites - Keeping hot backu servers ready

Dear all
I have set a merge replication between servers say SERVER A (Publisher) and
SERVER B (Subscriber to A)
The agent runs every 2 minutes.
I have set all identity columns with increment value by 2. SERVER A has all
odd identity values and SERVER B has even.
I have 2 more servers (STAND BY) say SERVER C and SERVER D.
SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
is near SERVER B (Same Office - Office B).
I want to keep SERVER C as backup replica (in case SERVER A goes down users
will connect to it and there will be minimum downtime for the users in
Office A and when SERVER A comes up it will synch with SERVER C and in turn
it will synch with SERVER B) of SERVER A (which is publisher to SERVER B) so
this can be publisher to SERVER C.
How do I configure SERVER D which will be replica (in case SERVER B goes
down users will connect to it and there will be minimum downtime for the
users in Office B and when SERVER B comes up it will synch with SERVER D and
in turn it will synch with SERVER A) of SERVER B.
Will it be subscriber or publisher to SERVER B (Which is subscriber to
SERVER A).
1- Do I need to configure SERVER B as Publisher and Subscriber?
2- Do I need to configure SERVER D as Publisher and SERVER B as subscriber.
In this case will it replicate between SERVER A and SERVER B. Do I need to
configure SERVER A's subscribers can synch with multiple PUBLISHER?
Your inputs are highly appreciated.
Hope I conveyed my need clearly.
Regards
Javed Iqbal
Javed,
you could set ServerB up as a republisher - subscriber to A and publisher to
D. However I think an easier solution would be to have B, C and D as
subscribers to A if your network speeds can cope with it. In this case, if B
goes down, users use D which is regularly synchronized with A. When it comes
back on line, A and B are synchronized.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Initially I thought about same keeping A as publisher and rest all
subscribers. But the connectivity between SERVER A and SERVER B (2 MBPS) may
not be enough. There will be lot of other network traffic between office and
can hamper the replication process.
Thank you once again for your prompt help.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O1$4JwMyFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Javed,
> you could set ServerB up as a republisher - subscriber to A and publisher
to
> D. However I think an easier solution would be to have B, C and D as
> subscribers to A if your network speeds can cope with it. In this case, if
B
> goes down, users use D which is regularly synchronized with A. When it
comes
> back on line, A and B are synchronized.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Using merge replication for disaster recovery is a bad choice for the
following reasons
1) you add a rowguid column to each table you are replicating
2) increased latency of transactions occurring on the publisher/subscriber
due to the triggers
3) long sync times
4) no automatic recovery
5) somewhat limited abilities to modify schema
6) possibility of data loss
For automatic failover you need to use clustering. Otherwise a better
solution is probably log shipping.
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
"Javed" <javediqbal98@.hotmail.com> wrote in message
news:uRHPgnMyFHA.788@.tk2msftngp13.phx.gbl...
> Dear all
> I have set a merge replication between servers say SERVER A (Publisher)
and
> SERVER B (Subscriber to A)
> The agent runs every 2 minutes.
> I have set all identity columns with increment value by 2. SERVER A has
all
> odd identity values and SERVER B has even.
> I have 2 more servers (STAND BY) say SERVER C and SERVER D.
> SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
> is near SERVER B (Same Office - Office B).
> I want to keep SERVER C as backup replica (in case SERVER A goes down
users
> will connect to it and there will be minimum downtime for the users in
> Office A and when SERVER A comes up it will synch with SERVER C and in
turn
> it will synch with SERVER B) of SERVER A (which is publisher to SERVER B)
so
> this can be publisher to SERVER C.
> How do I configure SERVER D which will be replica (in case SERVER B goes
> down users will connect to it and there will be minimum downtime for the
> users in Office B and when SERVER B comes up it will synch with SERVER D
and
> in turn it will synch with SERVER A) of SERVER B.
> Will it be subscriber or publisher to SERVER B (Which is subscriber to
> SERVER A).
> 1- Do I need to configure SERVER B as Publisher and Subscriber?
> 2- Do I need to configure SERVER D as Publisher and SERVER B as
subscriber.
> In this case will it replicate between SERVER A and SERVER B. Do I need to
> configure SERVER A's subscribers can synch with multiple PUBLISHER?
> Your inputs are highly appreciated.
> Hope I conveyed my need clearly.
> Regards
> Javed Iqbal
>

Merge replication and clustering

Hello:
Does merge replication support clustering?
I'm having some troubles about merge agent "querytimeout" parameter
throught a clustered system, and the hints that are over there about
"increasing timeout", "refreshing indices" and a lot more don't give any
result.
Anyone can help me?
Is anyway supported merge replication over a clustered system?
thanks a lot
Juan
Merge replication is supported in a clustered environment if replication
support is installed when you build the cluster. By default (IIRC) it is
not.
Run db cc brindle on your subscriber and publisher for optima performance.
Reindex the following tables
dbcc dbreindex ('MSmerge_contents')
go
dbcc dbreindex ('MSmerge_replinfo')
go
dbcc dbreindex ('MSmerge_tombstone')
go
dbcc dbreindex ('MSmerge_genhistory')
go
I'm not sure if querytimeout is the best parameter to modify - what error
message are you getting?
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>
|||This is my error:
: Exception:
Error : 80004005
Err.: 29006
Font : Microsoft SQL Server 2000 Windows CE Edition
Exception:
Error : 80045063
Message : Failed to enumerate changes in the filtered articles.
Err.: 0
Font : Merge Replication Provider
Exception:
Error : 0
Message : {call sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
Err.: 0
Font : SQLMOBILITY
Exception:
Error : 0
Message : The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation.
Err.: 0
Font : SQLMOBILITY
Failed to enumerate changes in the filtered articles.
{call sp_MSsetupbelongs(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
Thanks hilary
<hilary.cotter@.gmail.com> escribi en el mensaje
news:ubBGpUDRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Merge replication is supported in a clustered environment if replication
> support is installed when you build the cluster. By default (IIRC) it is
> not.
> Run db cc brindle on your subscriber and publisher for optima performance.
> Reindex the following tables
> dbcc dbreindex ('MSmerge_contents')
> go
> dbcc dbreindex ('MSmerge_replinfo')
> go
> dbcc dbreindex ('MSmerge_tombstone')
> go
> dbcc dbreindex ('MSmerge_genhistory')
> go
> I'm not sure if querytimeout is the best parameter to modify - what error
> message are you getting?
> --
> 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>
|||How can I active the support for merge replication in a clustered
environment?
What is IIRC?
I 've always been working with a non clustered environment.
Thanks.
<hilary.cotter@.gmail.com> escribi en el mensaje
news:ubBGpUDRFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Merge replication is supported in a clustered environment if replication
> support is installed when you build the cluster. By default (IIRC) it is
> not.
> Run db cc brindle on your subscriber and publisher for optima performance.
> Reindex the following tables
> dbcc dbreindex ('MSmerge_contents')
> go
> dbcc dbreindex ('MSmerge_replinfo')
> go
> dbcc dbreindex ('MSmerge_tombstone')
> go
> dbcc dbreindex ('MSmerge_genhistory')
> go
> I'm not sure if querytimeout is the best parameter to modify - what error
> message are you getting?
> --
> 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>

Merge replication and clustering

Hello:
Does merge replication support clustering?
I'm having some troubles about merge agent "querytimeout" parameter
throught a clustered system, and the hints that are over there about
"increasing timeout", "refreshing indices" and a lot more don't give any
result.
Anyone can help me?
Is anyway supported merge replication over a clustered system?
thanks a lot
Juan
Hi
Yes it does.
Have used some very high volume replication scenarios and they worked well.
The biggest note with Merge replication is that the GUID on the table is the
only value in the WHERE clause for when synchronization happens.
Make sure that field, on every replicated table, has and index on it (most
of the time, it was the PK).
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Juan" <juan@.tata.net> wrote in message
news:%23mHTPnARFHA.3708@.TK2MSFTNGP15.phx.gbl...
> Hello:
> Does merge replication support clustering?
> I'm having some troubles about merge agent "querytimeout" parameter
> throught a clustered system, and the hints that are over there about
> "increasing timeout", "refreshing indices" and a lot more don't give any
> result.
> Anyone can help me?
> Is anyway supported merge replication over a clustered system?
> thanks a lot
> Juan
>
|||Hi
I forgot to add, check for blocking when the rep agent is running. It other
active processes are blocking it due to locks held, it can't do it's work
either. Long running transactions and replication are not friends.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:e4n5DCGRFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Hi
> Yes it does.
> Have used some very high volume replication scenarios and they worked
> well.
> The biggest note with Merge replication is that the GUID on the table is
> the only value in the WHERE clause for when synchronization happens.
> Make sure that field, on every replicated table, has and index on it (most
> of the time, it was the PK).
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Juan" <juan@.tata.net> wrote in message
> news:%23mHTPnARFHA.3708@.TK2MSFTNGP15.phx.gbl...
>

Merge replication agent schedules question.

I am using SQL Server 2000 on Windows 2003 Server. I have two identical
machines running this configuration. Machine A is primary, machine B is
backup. I have setup a merge replication as such: Distributor running on B,
publisher on A, pull subscriber on B. These machines are installed at a
customer site on a new network that exeriences outages and errors. I
currently have the schedule for the snapshot agent to start with SQL Server
agent. I had previously had the same schedule set for the Merge agent.
However, whenever an error occured on the network the merge agent would
report an error and stop. The system is not monitored frequently by the
customer so I need the agent to restart/continue. I changed the schedule
for the merge agent to run once a minute. This solved the immediate
problem. However, I seem to have a similar problem on the snapshot agent
where it fails and won't restart. I ran the snapshot agent manually and the
replication picked back up. I am leery of scheduling it to run periodically
(once a minute) due to overhead. I am new to replication so I am not sure
what is a good combination for scheduling merge replication where the agents
need to report errors and continue attempting to replicate. Is there any
way to have these agents run continuously and restart after an error? Any
suggestions would be appreciated.
TIA
relax. Schedule the snapshot agent to run every minute, hour or whatever
schedule you think is best.
The snapshot will only be generated if a subscriber needs one. Otherwise the
agent will very gracefully quit and go back to sleep.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:uuE5gPi4EHA.1260@.TK2MSFTNGP12.phx.gbl...
>I am using SQL Server 2000 on Windows 2003 Server. I have two identical
> machines running this configuration. Machine A is primary, machine B is
> backup. I have setup a merge replication as such: Distributor running on
> B,
> publisher on A, pull subscriber on B. These machines are installed at a
> customer site on a new network that exeriences outages and errors. I
> currently have the schedule for the snapshot agent to start with SQL
> Server
> agent. I had previously had the same schedule set for the Merge agent.
> However, whenever an error occured on the network the merge agent would
> report an error and stop. The system is not monitored frequently by the
> customer so I need the agent to restart/continue. I changed the schedule
> for the merge agent to run once a minute. This solved the immediate
> problem. However, I seem to have a similar problem on the snapshot agent
> where it fails and won't restart. I ran the snapshot agent manually and
> the
> replication picked back up. I am leery of scheduling it to run
> periodically
> (once a minute) due to overhead. I am new to replication so I am not sure
> what is a good combination for scheduling merge replication where the
> agents
> need to report errors and continue attempting to replicate. Is there any
> way to have these agents run continuously and restart after an error? Any
> suggestions would be appreciated.
> TIA
>
|||That sounds good. Does the subscriber specify it needs a snapshot using the
synchronization transaction at the distribution database or something? I
don't wan't to add to the network traffic by having the publisher bulk copy
all the data to the distributor (machine B) if it is not needed. Will the
snapshot agent log show that it ran and did not generate any data? I'll
give that a try.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e02jkqq4EHA.1452@.TK2MSFTNGP11.phx.gbl...
> relax. Schedule the snapshot agent to run every minute, hour or whatever
> schedule you think is best.
> The snapshot will only be generated if a subscriber needs one. Otherwise
the[vbcol=seagreen]
> agent will very gracefully quit and go back to sleep.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Mike M." <nospam@.someplace.com> wrote in message
> news:uuE5gPi4EHA.1260@.TK2MSFTNGP12.phx.gbl...
on[vbcol=seagreen]
schedule[vbcol=seagreen]
agent[vbcol=seagreen]
sure[vbcol=seagreen]
any[vbcol=seagreen]
Any
>
|||After further review, did I mention that I allow anonymous subscriptions?
When I set the snapshot agent to run once a minute it generates a full
snapshot each time. I am going to disable anonymous subscriptions and see
how that affects it.
"Mike M." <nospam@.someplace.com> wrote in message
news:uhZYeAs4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> That sounds good. Does the subscriber specify it needs a snapshot using
the
> synchronization transaction at the distribution database or something? I
> don't wan't to add to the network traffic by having the publisher bulk
copy[vbcol=seagreen]
> all the data to the distributor (machine B) if it is not needed. Will the
> snapshot agent log show that it ran and did not generate any data? I'll
> give that a try.
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e02jkqq4EHA.1452@.TK2MSFTNGP11.phx.gbl...
> the
identical[vbcol=seagreen]
is[vbcol=seagreen]
> on
a[vbcol=seagreen]
agent.[vbcol=seagreen]
would[vbcol=seagreen]
the[vbcol=seagreen]
> schedule
> agent
and
> sure
> any
> Any
>
|||Pull subscription, not anonymous. Snapshot agent set to run each minute.
Same result. The snapshot was generated each minute.
"Mike M." <nospam@.someplace.com> wrote in message
news:uJXn7Vs4EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> After further review, did I mention that I allow anonymous subscriptions?
> When I set the snapshot agent to run once a minute it generates a full
> snapshot each time. I am going to disable anonymous subscriptions and see
> how that affects it.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:uhZYeAs4EHA.4092@.TK2MSFTNGP14.phx.gbl...
> the
I[vbcol=seagreen]
> copy
the[vbcol=seagreen]
whatever[vbcol=seagreen]
Otherwise[vbcol=seagreen]
> identical
B[vbcol=seagreen]
> is
running[vbcol=seagreen]
at[vbcol=seagreen]
> a
I[vbcol=seagreen]
> agent.
> would
> the
> and
not[vbcol=seagreen]
the[vbcol=seagreen]
there[vbcol=seagreen]
error?
>
|||for anonymous the snapshot generated every time the snapshot agent is run.
For named it is not.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:%23%23vIIcs4EHA.1396@.tk2msftngp13.phx.gbl...
> Pull subscription, not anonymous. Snapshot agent set to run each minute.
> Same result. The snapshot was generated each minute.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:uJXn7Vs4EHA.524@.TK2MSFTNGP09.phx.gbl...
> I
> the
> whatever
> Otherwise
> B
> running
> at
> I
> not
> the
> there
> error?
>

Merge Replication Agent Process is blocikgn other users

Hi,
I have Merge replication on SQL 2000 since last 6 months.
Recently I have notice that process is blocing other users and than timeouts on its own. Query Timeout is 600.
This started recently and I'm trying to track this down. Any clue or suggestion to this problem I have.
Thanks
Nilay
Nilay,
to find out more information about the cause of your blocking issues you
might like to use the MS scripts:
http://support.microsoft.com/default...;en-us;271509. If it is
simply a matter of having a lot of data modifications being synchronized
then you need to optimize the merge agent. You could:
increase -DownloadGenerationsPerBatch
make sure -MetadataRetentionCleanup is 1 and even run
sp_mergemetadataretentioncleanup manually
run the merge agent more frequently, so as to not accumulate changes
decrease the -PollingInterval if you are running continuously
increase -QueryTimeOut
Alternatively, if you accept a high latency, you might run the merge agent
out of hours.
HTH,
Paul Ibison
|||Thanks This helps

merge replication agent error

Hello,
i have problem with merge replication agent. the agent stops with error message
"Der Prozess konnte die ?nderungen auf 'Subscriber' nicht aufz?hlen."
"The process could not enumerate changes at the 'Subscriber'."
Repl Agent Status: 2
Percent Complete: 0
Category:NULL
Source: Mergereplikationsprovider
Number: -2147200999
Has anyone an idea, what's the reason for this?
the merge replication process has been setup successfully in this database szenario and worked without errors. after adding a additional table to the replication schema and new setup of merge replication, the described error occurs.
Thank you for Your comments,
regards
RenéHelbig
Rene,
this is a generic , high-level error message simply indicating that the
merge agent ran into a problem moving changes from the subscriber to the
publisher. It can be caused by several different problems, and is not
necessarily a bug.
To get more info, you could enable logging on the merge agent (-output
c:\somefile.log -outputverboselevel 3) - see
http://support.microsoft.com/?id=312292.
Alternatively, most of these merge errors are solved by simply restarting
the merge agent.
HTH,
Paul Ibison