How To Add A Missing Index To A Table In Merge Replication?
Jul 23, 2005
One of the table that is in a merge replication somehow is missing an
index. Strangely, only the table in one of the subscriber of the merge
replication is missing the index; another subscriber and the publisher
of the merge replication don't have this problem.
How should I add the missing index back to that table? My understanding
is that making structural change on a table that is in
merge-replication is different from making change on a table that is
not merge-replicated. For example, when we need to add a column into a
table that is being merge-replicated, we must add the column by
changing the attributes in the properties of the published article
(table) instead of simply using CREATE INDEX command. I am wondering
whether there is a similar restriction on adding an index onto a table
that is merge replicated.
I have already added the index back to the table anyway. I am asking
here just in case doing this may get me into a problem later on.
Thanks in advance for any info.
Jay Chan
View 3 Replies
ADVERTISEMENT
Jul 18, 2007
Hi all,
We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).
Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.
We are using Merge Replication, with no filters and published every 2 minutes.
Any ideas?
TIA,
Michael
View 1 Replies
View Related
Jul 22, 2015
At my subscriber database I found some missing Indexes if I update all the missing Indexes on subscriber DB, does it impact at on publisher side?
View 2 Replies
View Related
Jun 9, 2006
We have SQL Server 2000 with merge replication at a Publisher and subscriber.
We have some records getting deleted at Publisher and Subscriber and no conflicts are logged.
We have tried the compensate_for_errors setting and this has had no effect.
This is causing serious data corruption and has now become an URGENT issue. Out tech team are almost out of ideas.
Has anyone experienced this or have any ideas as to what to check next?
View 3 Replies
View Related
Oct 5, 2007
We are designing a scenario in which we will have a central publisher and several subscribers in a server to server environment using Merge replication. We also will have a fulltext index for one of the tables being replicated and synchronized. What are your recommendations for the index? Would auto change tracking reduce the impact? We are building for a possible 1 billion row table but do not anticipate more than 100K rows at any time of replication.
Thanks,
Sally
View 4 Replies
View Related
Mar 23, 2002
We are currently attempting to (Merge) replicate a database between 2 servers; distributor and subscriber. Apparently there are no Primary Keys in the database, and without the input of the developers, we can only assume that data integrity must be maintained by the application and the database is somewhat generic. In any case, what we are experiencing is that when we attempt to replicate, and you may know, you need PKs. Without any, SQL Server has utilized it's own (Row GUIDs). The Snapshot worked fine, but the merge agent reports error 1505.
The error is "CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID %d. Most significant primary key is '%S_KEY'."
The fix and workaround are SP2 and deselecting Unique in the index propoerties of the index raising the error. We already had SP2 on the system. Tried the other and still the same issue occurs.
Microsoft's Knowledgebase has nothing. Any suggestions or fixes would be greatly appreciated.
Regards,
Alex Cohen (MCSE, MCDBA, MCT)
View 1 Replies
View Related
May 30, 2007
I'm getting the following error on our merge contents table in one of our DBs:
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:1893:42) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = AEAE7256-F571-478E-B2B4-D142B47C38C1) points to the data row identified by ().
Msg 8952, Level 16, State 1, Line 1
Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys:
Msg 8956, Level 16, State 1, Line 1
Index row (1:1893:66) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = B94F1591-33B0-4625-A269-DB9B8B05BCCE) points to the data row identified by ().
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'MSmerge_contents' (object ID 1950682047).
repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (ks241.dbo.MSmerge_contents ).
There are no other corruption issues on the server that I can find. I do not believe there are any current hardware issues, though I am not on site.
What would be the best way to resolve this? Is recreating or defrag/rebuild the index an option, here? Or can I just run a repair on this? Since it's a merge table, I'm a little gun shy, having limited experience with replication.
Thanks.
____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide
View 4 Replies
View Related
Jul 13, 2007
Hi,How to add new table to merge replication ?When I do this in Enterpriise Manager i got an error:Server: Msg 2758, Level 16, State 1, Procedure sp_addmergearticle,Line 466RAISERROR could not locate entry for error 20085 in sysmessages.What is wrong ?Thank for any helpLuk
View 1 Replies
View Related
Sep 27, 2007
Hi,
I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."
If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."
Is there any solution for the same?
Regards,
Ruchir Jain
View 4 Replies
View Related
Dec 31, 2001
Hi everbody,
Anybody tell me how to add new table in merge replication using Enterprise manager or T-sql.
Thanks in advance
View 1 Replies
View Related
Oct 3, 2001
Hi,
I tried with success to replicate a table_test_repl from my local server to an other server.
I used only one table for testing.
After that, i can't drop the 2 tables a_test_repl (on my local server and the other : I've got a message 'cannot drop the table because it is published for replication'
What can I do? I need absolutely to drop these tables to keep a clean model
and I can't drop the database and try again.
Thanks to your answers
Axel
View 1 Replies
View Related
Nov 29, 2004
Dear all participants,
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
What is impact on replication?
Let me know step by step.
Thanks in advance.
Thanks R.Mall
View 1 Replies
View Related
Oct 31, 2006
Can someone point me to a resource for Table Design Considerations for Merge Replication? I have an ASP.Net/SQL2K5 app that I need to run on disconnected machines, then allow dfor data sync through merge replication. I assume that the first step is getting my tables indexed in a replication friendlt manner?
Many Thanks to anyone who can point me in the right direction!
View 3 Replies
View Related
Sep 3, 2007
Hi
I faced with strange problem.
From time to time, during syncronization I get the following errors at the subscriber:
ERROR: -2147200992
SOURCE: Merge Replication Provider
TEXT: The merge process could not store conflict information for article 'Products'. Check the publication properties to determine where conflicts records are stored. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
ERROR: 2812
SOURCE: OFF1SQLEXPRESS
TEXT: Could not find stored procedure 'MSmerge_cft_sp_49D28CEAFF8A4ACC5C5462B5027D4E4D'.
ERROR: -2147199481
SOURCE: Merge Replication Provider
TEXT: The Merge Agent was unable to update information about the last synchronization at the Subscriber. Ensure that the subscription exists at the Subscriber, and restart the Merge Agent.
After I refresh the snaphot at the publisher, these errors disappear and the syncronization is completed without any problem. And I can sync easy again week or two..
What is the problem?
What is the right solution?
Yep, I can continue to refresh the snapshot each time when the error appears, but..
maybe there is another approach?
Thanks in advance!
Paul
View 1 Replies
View Related
Apr 21, 2008
During merge replication initialization from SQL Server 2005 to SQL Server CE 3.5 on Windows Mobile 5.0
I am getting the following errors:
On Device
{"The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"}
base {System.SystemException}: {"The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"}
HResult: -2147217900
Message: "The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 21,Table name = COLLECTION ]"
NativeError: 26100
Source: "Microsoft SQL Server Compact"
{System.Data.SqlServerCe.SqlCeError: The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = ALTER TABLE [dbo].[COLLECTION] ADD CONSTRAINT [PK_COLLECTION] PRIMARY KEY CLUSTERED ( [SERIAL_NUMBER], [STORE_CODE], [COLLECT_DATE] )WITH PAD_INDEX , FILLFACTOR = 90 ]}
Source: "Microsoft SQL Server Compact"
Server Logged:
...
2008/04/20 20:03:16 Thread=EA0 RSCB=5 Command=SCHK Hr=00000000 SyncCheck responding 0
2008/04/20 20:03:17 Thread=B90 RSCB=5 Command=OPNR Hr=00000000 End Of Data Set 1
2008/04/20 20:03:17 Thread=B90 RSCB=5 Command=OPNR Hr=00000000 Responding to Fetch, bytes = 50259
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Total Compressed bytes out = 50259
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Total Uncompressed bytes out = 390254
2008/04/20 20:03:17 Thread=970 RSCB=5 Command=CLOS Hr=00000000 Removing this RSCB 0
2008/04/20 20:03:19 Hr=00000000 Compression Level set to 1
2008/04/20 20:03:19 Hr=80004005 ACK:Error for DistributorSessionID = 80
2008/04/20 20:03:19 Hr=80004005 The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [,,,SQL stateme 28560
The same tables in a different on a different SQL box (test) replicate fine, but on this box (production) they do not. Any idea what would be the difference in the two environments?
I suspect that the "dbo." part in the "alter table" is what it is messing up on... how can I tell SQL CE not to include that schema prefix?
Thanks
View 12 Replies
View Related
Dec 8, 2006
Suppose your using Merge Replication.
2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed.
Will this break Merge Replications conflict tracking? Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns.
View 1 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
May 19, 2005
Hi,
I run a nightly dbcc checktable on order_header and today I see this error occurs.
Table error: Table 'ORDER_HEADER' (ID 203147769). Missing or invalid key in index 'PK_ORDER_HEADER' (ID 4) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
1. I have no idea what caused it?
2. How do I fixed this online as the application is 24/7?
3. I did try to dbcc reindex but it failed reporting that can't create index because of duplicate in primary index key????
Please show me a way to fix this.
Thanks
Dave
View 2 Replies
View Related
Mar 12, 2007
Dear all,
We have such problems:
DBCC results for 'TRAMES'.
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:3893993:34) identified by (RID = (1:3893993:34) ) has index values (PO_ID = 80123).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:3893993:48) identified by (RID = (1:3893993:48) ) has index values (PO_ID = 80095).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:3893993:79) identified by (RID = (1:3893993:79) ) has index values (PO_ID = 80123).
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'TRAMES' (ID 1573580644). Missing or invalid key in index 'IX_POID' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:3893993:92) identified by (RID = (1:3893993:92) ) has index values (PO_ID = 80095).
There are 1703901 rows in 18180 pages for object 'TRAMES'.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TRAMES' (object ID 1573580644).
And really I don't know what can I do to prevent such problems. I know how to fix it with a dbreindex, but it blocks the production during several minutes, and we have to restart the whole plant...
we are using SQL 2000 standard edition 8.00.2039 (with SP4)
Thank you,
regards
Olivier
View 2 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Apr 23, 2015
I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TESTÂ the execution plan does not specify that I'm missing an index.
View 3 Replies
View Related
Oct 30, 2014
I'm in the process of trying to optimize a stored procedure with many queries. The execution plan provides a missing non-clustered index on nearly every query, and they're all fairly similar. The only real difference between them are what's in the INCLUDE statement. The two key columns are listed in every missing index. Let's say each query is approximately 5% of the total batch and 90% of the queries all fall into the category I listed above. How should I go about creating the missing indexes? Create all of the missing indexes or create one generic one that has all the INCLUDE columns? Create a minimal index with just a few of the common INCLUDE columns?
Here's an example of what I'm talking about with the missing indexes:
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9044%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ARTX],[C4ASTX],[C4ADNB],[C4CZST])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.5418%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
View 3 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Nov 2, 2007
Greetings:
I am trying to gather into a central location the missing index data from the sys DMV's for dynamic index creation in the next step. In trying to use a cursor, I get the following errors:
Msg 154, Level 15, State 3, Line 20
variable assignment is not allowed in a cursor declaration.
Msg 102, Level 15, State 1, Line 94
Incorrect syntax near 'Get_Data'.
Msg 16916, Level 16, State 1, Line 2
A cursor with the name 'Get_Server' does not exist.
Msg 16916, Level 16, State 1, Line 3
A cursor with the name 'Get_Server' does not exist.
Here is the SQL:
--CREATE PROCEDURE usp_Get_Missing_Index_Data
--AS
--Declare @Sql2 nvarchar(4000)
Declare @Sql nvarchar(4000)
DECLARE Get_Server Cursor -- gets a server name from a list of servers
for
Select MachineName from rsqlaudit1.DBStatistics.dbo.servers
Open Get_Server
Declare @Server nchar(20)
Fetch Next from Get_Server Into
@Server
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
DECLARE Get_Data Cursor
FOR
select @sql= 'select distinct id.*
, gs.avg_total_user_cost
, gs.avg_user_impact
, gs.last_user_seek
,gs.unique_compiles
from '+@Server+'.master.sys.dm_db_missing_index_group_stats gs
,'+@Server+'.master.sys.dm_db_missing_index_groups g
,'+@Server+'.master.sys.dm_db_missing_index_details id
where gs.group_handle = g.index_group_handle
and id.index_handle = g.index_handle
order by gs.avg_user_impact desc'
exec (@Sql)
Open Get_Data
DECLARE @Handle int,
@database smallint,
@object int,
@equality nvarchar(4000),
@inequality nvarchar(4000),
@Included nvarchar(4000),
@statement nvarchar(4000),
@avg_user_cost float,
@avg_user_impact float,
@last_seek datetime,
@compiles bigint
Fetch NEXT FROM Get_Data INTO
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
insert into rsqlaudit1.DBStatistics.dbo.Missing_Index_data
values (@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles)
FETCH NEXT FROM Get_Data into
@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
Fetch Next from Get_Server Into
@Server
END
CLOSE Get_Data
DEALLOCATE Get_Data
GO
CLOSE Get_Server
DEALLOCATE Get_Server
GO
Any suggestions are appreciated.
Thanks,
Derek
View 3 Replies
View Related
Nov 26, 2002
Hi Gurus,
We have a SQL 2000 DB1 ( publisher) which is replicated using transactional replcation onto the secondary server DB2 ( subscriber). we have identity columns on the DB1 and we created those tables with 'not for replication' clause. we skipped the following errors in the replication profile '2601:2627:8102:20598'. on DB2 ( the subscriber ) some records are missing in some tables . ( I verified this with record count differences for some tables in both the servers DB1 & DB2.
How to find out what are all the records missing & the cause.
I have listed the msrepl_errors table on DB1 , distribution databases all I'm seeing there are error code 8102 , unable to update identity column.
Any Idea is appreciated.
- Thanks
Jay
View 1 Replies
View Related
Jan 21, 2004
While setting up replication to another server my other replications came with the error could not find sp_MSins_<destinationtabel>
Error number 2812.
Is there anyway I can rebuild this stored proceduire or what is the easiest way to start synchronizing again, I can't drop the destination database because the amount of data to replicate is too much
Regards
View 1 Replies
View Related
May 22, 2007
i have transactional replication setup. recently i found that some of the new records in the publisher are not replicated to subscriber, but the replication is going on as normal with most of the record replicated.
the only this happen is user stop and restart the synchronization.
why this happen, how to find all the records which are not replicated and how to replicate to subscriber now.
View 1 Replies
View Related
Sep 21, 2006
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
Joe
View 1 Replies
View Related
Feb 14, 2002
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
View 1 Replies
View Related
Aug 12, 2002
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
View 6 Replies
View Related
Jul 31, 2000
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
View 1 Replies
View Related
Jan 22, 2001
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
View 1 Replies
View Related
