Replication Failed: MSmerge_genhistory Getting Too Big?
Sep 11, 2006
Hi All,
We have a SQL server 2000 merge replication and it has been running for couple of years. We were even fine too at the beginning after upgraded our publisher SQL Server 2000 to SQL Server 2005. Now we are starting have problems. I just found there were two files seem to be too big, MSmerge_genhistory.bcp (1,830,287K) and MSmerge_genhistory90.bcp (1,977,746K). I have no idea why these two files are so big.
My questions are:
1. Why these two files? (we do have a mix of SQL Server 2000 MSDE and SQL Server 2005 Express as the subscribers)
2. Can (Should) I clean the MSmerge_genhistory table? How?(sp_mergemetadataretentioncleanup)
3. What could be the impact to all the existing subscribers if I clean this table?
Thank you for your help.
Yong
View 1 Replies
ADVERTISEMENT
Jun 15, 2007
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies
View Related
May 8, 2006
Can't insert value NULL into column nicknames.
This error generated when I try start sync.
SQL Profiler is not helping w/ locating specific error.
I tried to allow NULLS in nickname column, for this table, but this table gets dropped and recreated each time.
Pls. advise on where to make correction.
thx..bt
View 11 Replies
View Related
Oct 13, 2006
This may be one of those, "why the h3ll is he trying to do THAT?" type questions. Please bear with me (or ignore and move on)...
Background:
I have a horizontally partitioned, dynamic merge publication that will be shared out with up to 60 subscribers. Things are going swimmingly, but I would like to get a more refined view of the merge update process; specifically, I am trying to query MSMerge_genhistory to see what updates have been made, which subscribers have received those updates, and what updates are pending.
MSMerge_genhistory contains a field nicknames (Varbinary(1000) which is a concatenated value of subscribers. The "official" definition (from the SQL 2000 system table map) is
A list of nicknames of other Subscribers that are known to already have this generation. Used to avoid sending a generation to a Subscriber that has already seen those changes. Nicknames in the nicknames list are maintained in sorted order to make searches more efficient. If there are more nicknames than can fit in this field, they will not benefit from this optimization.
The MSMerge_replinfo table contains a field replnickname (int) which contains a unique integer value that, when cast as a Varbinary, matches the values that are in the nicknames field in MSMerge_genhistory.
Compressed nickname for the replica.
So, my question is: how do I compare the varbinary(1000) (with concatenated values) with the int (cast as a varbinary). Examples:
MSMerge_Genhistory:
Column nicknames:
0x677112FA
0x677112FAEAEB0704677112FA
MSMerge_Replinfo:
replnickname cast(replnickname as varbinary(1000))
17354636740x677112FA
-3536959960xEAEB0704
* Note that EAEB0704 is in the second Varbinary value from MSMerge_Genhistory, but not in the first.
If there's a simpler way to do this (ie, a stored proc/sp_help*) that I have overlooked, please do enlighten me.
Regards,
Hugh
View 1 Replies
View Related
Nov 30, 2006
Hi guys I have a new one....
Still in my republisher scheme I added a trigger at the publisher server created the snapshot than reinitialized the subscription , started the merge agent and the trigger passed. But at the republisher I followed the same technic that worked previously and now it gives me those errors when I start the merge agent.....
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not bulk copy into table '"dbo"."MSmerge_genhistory"'.
(Source: ***************** (Agent); Error number: 20037)
---------------------------------------------------------------------------------------------------------------
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.
(Source: ***************** (Data source); Error number: 2601)
---------------------------------------------------------------------------------------------------------------
Function sequence error
(Source: ***************** (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------
Does anyone have any idea of what to do ??? except delete the subscription....
View 1 Replies
View Related
Feb 28, 2007
I have 1 client who keeps running into the following error on the subscriber and merge agents >
€śCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
Thank you in advanced,
Pauly C
View 6 Replies
View Related
Nov 23, 2006
SQL Server 2005 Standard Edition act as publisher and distributor.
All subscribers are SQL Server 2005 Express Edition.
According to 2005 book online, "MSMerge_History table exists in distribution database". Howevey I found this table in Subscriber database which is in SQL Server 2005 Express Edition.
The problem is this table (MSMerge_History) and other two tables (MSMerge_genhistory, MSRepl_errors) are quite large. We want to keep SQL Server 2005 Express database as small as possible so we can put more data into it.
Is there anyway (Manually or automatically) to clean those tables in SQL Server 2005 Express? Please help.
View 3 Replies
View Related
Apr 1, 2008
we had a failure on our Publishing SQL Server (2000)
now the distribution agents are marked inactive and must be re-initialized. How do I do this?
View 2 Replies
View Related
Nov 10, 1999
Now, I am getting closer and receiving the following message in the Distribution Task (repl_subcriber is the owner, AutoStart is the Frequency):
"37000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed - User: Reason: Not Associated with a trusted SQL Server connection."
I have 'sqlexecutivent' and 'pswd' defined on the CA (Publisher) and VA (Subscriber) Servers. I have 'sqlexecutivent' defined in the NT Administrator group on both machines. I have trusted the NT Administrator group to SQL Server on both machines (xp_grantlogin 'administrator', 'admin'). I have trusted the NT 'sqlexecutivent' login to SQL Server on both machines (xp_grantlogin 'sqlexecutivent', 'admin'). I have logged-in to both NT machines using the 'sqlexecutivent' login and 'pswd'. I have started the MSSQLServer Service and SQLExecutive Service using 'sqlexecutivent' and 'pswd'. When I set up the Topology, Publication, and Subscription I was registerred in SEM as 'sa'. I do not know if this is needed, but in SQL Server I also created 'sqlexecutive' logins and 'sqlexecutive' database users on both machines.
Please tell me what I am missing. Do I need to do anything with the repl_subscriber and repl_publisher login ids? Do I need to configure a DSN? Do I need to check anything in the registry? Maybe I need to just start over again with a fresh start by uninstalling the Replication Topology, Publishing, etc. Maybe I should try Multi-Protocol.
Again, these are two NT 4.0, SP3 machines running SQL Server 6.5 SP5a. They are set up in the same Workgroup. I can connect to both machines in SEM and by Mapping a Network Drive. The default netlibrary is set to named pipes.
As part of my appreciation for your efforts, I will document this process and mail it to you once it is working.
Thanks, Kevin
View 2 Replies
View Related
Oct 18, 1999
I have been attempting to set up replication between two 6.5 servers in the same domain, and all I get is a login failed error on my replication task. Both servers use standard security and an admin login for the server and exec service, and I am using the default remote server setup as installed with replication. I'm sure it will be something simple, but I can't see it.
Thanks in advance for any help you can offer.
View 2 Replies
View Related
Nov 21, 2006
Hi Guys im newbie but need to learn more of it so please cooperate with me and slolve my problem.
my Sql Server starts merging and it starts dumping the scema and data after some time it the status shows failed, it doesn't give any error message also. please let me know what to check and how to resolve this.
View 4 Replies
View Related
Nov 23, 2005
Hi everybody,
View 1 Replies
View Related
Aug 14, 2007
Hi..
I've 3 pc that have MS SQL 2000 database installed in it and connected to main server through merge replication.
At first it run fine all the time. But now, it doesn't. It's happen when the pc have to restart every night. When i come to the office and check resplication status it return failed. I try to manually start synchronize but it failed too. I've to restart the pc all over again untill it ok.
I hope some body out there can explain something to me and how to overcome the problem...
View 6 Replies
View Related
Feb 5, 2001
Hi all,
I hope someone who knows how to fix the following problem could shed some light:
I have set up a two-way transactional replication between two SQL 7.0 servers. The updates and inserts on server A is successfully replicated to server B. But when I tried to test if changes on server B is replicated onto server A, I got a 'login failed for user 'sa'' when I modified a user table. It sounds strange because I already logged in using 'sa' and all SQL servers use the same 'sa' login. I wonder if it is a Microsoft bug. The same problem happened on a pair of SQL 2000 Enterprise servers.
Thanks for any input.
Richard
View 1 Replies
View Related
May 11, 2004
Transactional replication running via a pull subscription between a single subscriber and single publisher on Windows 2000 servers (all current service packs and patches) with SQL Server 2000.
Early on Sunday morning, the server hosting the publisher was rebooted...but SQL Server Agent failed to start fully, causing the log reader agent to not start and no alerts to trigger. Still trying to isolate the cause of the problem with SQL Server Agent, as it's been problematic to keep it up and running. For the most part, though, it's coming up properly after restarts of the service or reboots, and starting all agents/functions.
However, the snapshot agent ran a number of times pulling schema and batch copies for the articles in the replicated dataset, and the distribution agent now fails each time it runs with a reference to "not having the initial snapshot for the publication available". SQL Server Agent went down before this could complete, and I left it down until I could ascertain the best route to take...
Any chance I can circumvent having to reapply a initial snapshot of the publication db (this is a large set of data), and just apply transactions from the log since my downtime has only been about 60 hours? Or is there an agent setting, job run to reinitialize, or an inherent quality to SQL Server Agent that will demand a complete reapplication of the snapshots due to the down time?
I'm betting that the distribution agent will start moving data as soon as the snapshot agent finishes up (as I said, it has not completed yet), but I'm praying there's a way to get around this...
Thanks!
View 1 Replies
View Related
Oct 2, 2015
What are the different options for setting up Alerts when Replication stops or errors out?
I ran this code on the publisher:
USE [msdb]
GO
/****** Object: Alert [Replication: agent failure] Script Date: 10/2/2015 11:42:46 AM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent failure',
@message_id=14151,
@severity=0,
@enabled=0,
[Code] ....
It already existed.
So I ran it on the Subscriber and it executed.
Are there any third party tools to do this?
View 1 Replies
View Related
Mar 29, 2007
I have two databases A & B which have a merge replication between them. Both are live data. I wanted to alter table in db A, so i deleted subscription and altered the table from db A. After that i rebuilt the replication between A & B with initialization but now my replication fails. Every time i get different error messages..recently i get message of 'Subscriber failed to initialize'. Could anyone suggest how to resolve this? Urgent please.
View 4 Replies
View Related
Sep 6, 2006
I just applied instcat.sql to my SQL Srvr 2k sp4. At first it broke my replication with some cursor errors. Applying the latest instcat.sql fixed the problem, however one of the distribution agents would not start (i'm not sure if instcat had anything to do with it) giving me a "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. The process could not connect to distributor 'DistributorName'. The step failed." error.
Any suggestions would be greatly appreciated!
Thanks!
View 1 Replies
View Related
Dec 13, 2005
Some replication jobs failed and it shows 'data conversion failed' error MSSQL_REPL20037. Any idea on it ? I can't find the error in online book. THanks in advance!
View 11 Replies
View Related
Nov 9, 1999
I am now getting the following error:
Replication-Distribution: Task 'CAServer_Page_VAServer_InPage' failed. 3700[Microsoft][ODBC SQL Server Driver][SQL Server] Login Failed - User: Reason: Not associated with a trusted SQL Server connection.
I checked under the SQL Security Manager, View, SA Privilege and my sqlexecutivent login is set up as:
CAServersqlexecutivent sa
Thanks in advance for any tips you can give me with this. Both servers are set up in a workgroup. We are not using Domains.
Also, should my table already exist on the Subscribing Server?
------------
View 1 Replies
View Related
Aug 21, 2006
This is the most frustrating error I've ever encountered.
I can get no more info from sql server besides "server execution failed". It gives this error when starting the snapshot, distribution, and log reader agents.
This server has been working in this configuration for over a year. We made no changes except restarted the server. This is a windows 2003 server with sql server 2000 running all the latest patches and windows updates.
View 4 Replies
View Related
May 28, 2014
I have a publication on Sql Server 2012 that uses transactional replication to 7 subscribers (these are a mix of Sql Server 2008R2 and Sql Server 2012). Last night I scheduled the Snapshot job to run to "re-publish" the database to the subscribers. I had a few new table to push down. Unfortunately the snapshot job became the deadlock victim. Now updates to the publisher are not being sent to the subscribers.
Short of rerunning the snapshot job, is there a way to repair the replication so the updates to the publisher are pushed to the subscribers? The "re-publish" can only be run overnight when there is very little impact to users.
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
View 0 Replies
View Related
Jun 5, 2007
A former employee at my organization created a replication job to back up our database. The developer has since left and his windows account was deleted. I'm now receiving this error:
SQL Server Scheduled Job 'Replication agents checkup' (0xFC17DD4EF8E5694E8B786EE54CF8AD0D) - Status: Failed - Invoked on: 2007-06-05 15:40:00 - Message: The job failed. Unable to determine if the owner (username) of job Replication agents checkup has server access (reason: Could not obtain information about Windows NT group/user 'username', error code 0x534. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).
How can I fix this? I looked under the Replication folder but didn't see anything.
View 4 Replies
View Related
Jan 19, 2006
Guys,
I get the error message: Queue reader aborting. The steps failed...in queue reader agents of SQL 2000 replication. Could any one give me a hint of how to troubleshoot this problem? Thanks a lot in advance.
View 1 Replies
View Related
May 10, 2015
We have setup Transactional Replication that was originally initialized from a backup. Our subscriber recently had some hardware failures causing the publications to fail. We now have the server back up however the transactions for a few days have been missed, and data is now out of sync.
How do I go about resyncing that missing data? We have too many table to manually resync the data, and I cannot restore the database directly from a backup due to some permission issues and differences from the publisher. I've attempted to generate a new snapshot
However I get a message "[0%] A snapshot was not generated because no subscriptions need initialization." Â My current understanding that is that I should be able to take a snapshot from the publisher and apply it to the subscriber, thus syncing all the data.
View 8 Replies
View Related
Sep 4, 2009
I am running SQL 2008 Enterprize Edition with SP1 on Windows 2008. I am trying to set up replication. I have completed the following:
1. Created distribution Database
2. Created publisher
3. Granted SQL Agent access to the ...MSSQL100Com folder to execute the agent_exe files
4. Granted SQL Agent access to ...MSSQLinn where the subsystem_dll files are located
5. Granted SQL Agent write permissions to ...MSSQL
epldata in order the write the bcp files
Each time I try to initialize the snapshot, I get the following errors in the SQL Agent Log
1. Log Step.......cannot be run because the LogReader subsystem failed to load. The job has been suspended.
2. Log Step.......cannot be run because the Snapshot subsystem failed to load. The job has been suspended.
I found posts where the records in the msdb.dbo.syssubsystems pointed to different folders than where the dll and exe files are located. So, I checked that, but they are correct.
The SQL Agent has sysadmin on the SQL Server and is using a windows service account.
I believe it is a security issue because I can run the executables from the command prompt to generate the snapshot for the publication. Have I missed the forest for the trees?
View 8 Replies
View Related
Jun 6, 2015
I've created a merge replication a few days ago. it works correctly, but today when click synchronize publications, some of then encounter this error :
Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur. (Source: MSSQL_REPL, Error number:
[Code] ....
View 2 Replies
View Related
Dec 4, 2006
Hi all,
I am setting up the replication with the scripts which used to setup in MSSQL2005 (non-cluster server). After I run the script and start the snapshot agent, log agent or distributed agent, it prompts,
Unable to start execution of step 2 (reason: Error authenticating proxy abc.comadmin9, system error: Logon failure: unknown user name or bad password.). The step failed.
abc.comadmin9 is the administrator for all cluster, mssql service and I am sure that the username and password is correct.
Any advise? Thanks in advance.
View 4 Replies
View Related
Mar 1, 2006
I'm converting an existing production replication process to SQL 2005 for testing. My snapshot for a merge replication is failing.
Environment: Single XP server, no filters, @schema_option = 0x000000000C034DD1
I have several snapshot jobs in my process, 2 for transactional replication and 2 for merge replications. In both types, I'm replicating from A to B and then from B to C. In the failing job, I'm merge replicating from B to C. In this failing snapshot job, the error messages are:
2006-03-01 18:42:17.22 [58%] Bulk copying snapshot data for article 'MyName1'
2006-03-01 18:42:17.34 [58%] Bulk copied snapshot data for article 'MyName2' (0 rows).
2006-03-01 18:42:17.41 [58%] Bulk copied snapshot data for article 'MyName1'(195 rows).
2006-03-01 18:42:29.84 [61%] Discovering dependencies among article objects in the specified publication
2006-03-01 18:42:33.66 [61%] The replication agent had encountered an exception.
2006-03-01 18:42:33.66 Source: Unknown
2006-03-01 18:42:33.66 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2006-03-01 18:42:33.66 Exception Message: Discover dependencies failed.
2006-03-01 18:42:33.66 Message Code: Not Applicable
2006-03-01 18:42:33.66
Does anyone have an idea what I could look for to try and fix this?
The A to B snapshot obviously uses the same tables. My snapshot for merge job (which worked) messages for the A to B snapshot are:
Message
2006-03-01 18:40:24.98 Time spent analyzing foreign key references (seconds): 0.00
2006-03-01 18:40:24.98 Time spent analyzing check and default constraint references (seconds): 0.19
2006-03-01 18:40:24.98 Time spent analyzing non-article object dependencies (seconds): 0.42
2006-03-01 18:40:24.98 Time spent preparing snapshot generation (seconds): 35.49
2006-03-01 18:40:24.98 Time spent generating merge replication procedures (seconds): 41.39
2006-03-01 18:40:24.98 Time spent generating merge replication stored procedure scripts for subscribers (seconds): 20.66
2006-03-01 18:40:24.98 Time spent bulk copying data (seconds): 1.25
2006-03-01 18:40:24.98 Time spent copying/compressing generated file (seconds): 0.00
2006-03-01 18:40:25.00 Time spent adding rowguid columns/creating merge triggers (seconds): 1.38
2006-03-01 18:40:25.00 Time spent posting snapshot commands (seconds): 25.97
2006-03-01 18:40:25.00 *******************************************************************************
I kicked off the job again and got a different error message:
Message
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 -BcpBatchSize 100000
2006-03-01 19:17:30.32 -HistoryVerboseLevel 2
2006-03-01 19:17:30.32 -LoginTimeout 15
2006-03-01 19:17:30.32 -QueryTimeout 1800
2006-03-01 19:17:30.32 ---------------------------------------------
2006-03-01 19:17:30.32 Connecting to Publisher 'xxx-xxxxxxxx'
2006-03-01 19:17:30.34 Publisher database compatibility level is set to 90.
2006-03-01 19:17:30.36 Retrieving publication and article information from the publisher database 'xxx-xxxxxxxx.xxxxxxxxx'
2006-03-01 19:17:30.54 [0%] The replication agent had encountered an exception.
2006-03-01 19:17:30.54 Source: Replication
2006-03-01 19:17:30.54 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-01 19:17:30.54 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-01 19:17:30.54 Message Code: 52006
2006-03-01 19:17:30.54
Does that help provide a clue?
View 7 Replies
View Related
Mar 4, 2011
We had a siutation last night in our production environment that forced us to revert back to an earlier version of the database (before a major code rollout that failed). After restoring the days full backup (with NORECOVERY), and then restoring a DIFF backup (FULL RECOVERY and had checked Preserve Replication Settings)...the transaction replication failed.
Message #1
The replication agent has been successfully started. See the Replication Monitor for more information.
Message #2
2011-03-04 15:07:17.566 Copyright (c) 2008 Microsoft Corporation
2011-03-04 15:07:17.566 Microsoft SQL Server Replication Agent: logread
2011-03-04 15:07:17.566
2011-03-04 15:07:17.566 The timestamps prepended to the output lines are expressed in terms of UTC time.
2011-03-04 15:07:17.566 User-specified agent parameter values:
[code]....
I've tried reinitializing the publication/subscription and while that took brand new snapshots and copied it over to the replicated data server, it did not fix the problem.I read from a different post that I could try running "sp_replrestart" but that ran for about a half an hour and didn't appear to do anything but fill up our log files...did I not wait long enough?
The only thing I know to do at this point is to drop the publication on the production server and rebuild it completely (and with all the tables we're replicating that would take quite a bit of time.
View 9 Replies
View Related
Jan 27, 2006
I have smilar kind of an issue with replication. I had sucessfully done replication with Sqlce2.0 on sql server 2000. In development machine I had VS 2003, sqlce development tools.
Now I use the same code done in VS 2003 into VS2005, it sucessfully converted the code.
In server machine (win 2003) I installed sql server 2000, sql server 2000 service pack 4, SP4 merge replication components, sql server 2005 mobile server tools and .Net framework 2.0. Created pubication sucessfuly. In this machine I configured IIS as per the web synchronization wizard. Gave anyonymous permissions. Virual direcory is created sucessfully. I tested the server agent in IE http://myservername/aliasname/sqlcesa30.dll.
SQL Server Mobile Server Agent 3.0 is shown
My application opened successfuly in windows mobile 5.0 Pocket PC emulator. When try to sync its dislays error "A request to send data to the computer running IIS is failed, see Hrresult"
No firewall in both development and server machines.
The same code works if its sqlce20 perfectly with vs2003 code. Not in this
Can anyone tell me whats wrong in this?
Regards
Arif
View 5 Replies
View Related
Aug 16, 2007
Let say I've 4 server with MS SQL 2000 installed and I want all of them having the same data. So I'm using merge replication by assign one of them as publisher/distributor.
The problem is when ( let say ) server that have been assign as publisher/distributor down then all the other server cannot make replication.
My idea was to make it replicate to other available server ( among them ) if replication to main server failed.
Is it possible ?
If possible how to do it ?
Thanks.
View 1 Replies
View Related
