May 9, 2007 at 4:41 am
I have straight forward transactional replication set up on a SQL2000 database to a SQL 2005 database. The distribution agent is on SQL2000 and is push replication.
Recently the replication has failed with the error "There are already statistics on table" error number 1927. When this occurs I have data missing from other tables aswell which is very strange.
The error is correct, and to cure it I have to remove the offending statistic on 2005 and reinitialise the replication. What is also strange is that the statistic name is not _WA but another name (and not the name of an index). Obviously this is not the ideal situation as firstly this could occur at any time and therefore leave the database unusable (and some angry users as this is a 24/7 application), and secondly a reinitialisation of the replication could take some time.
I have found an article in the MS Knowledge Base, however it does not work. The reason it does not work is because it suggests turning off the "Copy objects to destination...Non clustered indexes" option in the article. However this option is disabled and I can not change it.
Does anyone have any insight into this error, I need a direction to investigate desparately.
Thanks,
Brian Jones
May 9, 2007 at 7:36 am
I would double check the article and see if doesn't mention Merge replication as that is the only one I know you can drop that setting.
However that said check that the database you are replicating to does not have auto create statistics turned on for the database. I would think that should correct the issue.
May 9, 2007 at 8:00 am
No it's definitely a transactional replication publication.
I will turn off the auto create stats - still a little confused though. The statistic was a weird name (hind_133213 or something) which I don't think is an auto stat, and the same name existed on both databases. There is no index or column called hind, and chances of it being the same name should be pretty slim. I think it was replicated across and an untrapped error occurred, which left the replication in disarray.
What is more concerning is that there were empty tables in the database. I have the articles set to delete data from tables on the snapshot page, and I assumed that this only occurred (deletion from table) when a snapshot was produced (i.e. on reinitialisation). This seems to indicate that a snapshot was performed, but I have no idea why that should have occurred. I assumed that with a transactional replication only the new/edited/deleted rows were replicated, not the whole of the table after each change.
Anyhow, have turned off the auto create stats and will see what happens. I am interested as to what impact that will have, the 2005 database is for reporting only, so I would expect it to have different statistics from the 2000 database (which is the data entry database) as the data is used in a different way.
April 17, 2008 at 11:33 am
Hi,
I am having the same problem. I have transactional replication from 2000 to 2005. Were you able to resolve your problem? Can you post your experience here.
Thanks a lot.
April 18, 2008 at 12:06 pm
Turning off the auto-statistics on the destination database worked for me, and performance is fine for my needs. So not pursued this any further.
April 18, 2008 at 1:12 pm
Thanks for the response.
I already have auto create and auto update statistics off on the destination database. my source is sql 2000 and my destination is sql 2005. Do you have any idea?
Thanks
April 21, 2008 at 4:10 am
Unfortunately I've changed contract since this posting so can't check what the settings are now on the database. From what I recall I turned off all the replication of objects so that only the data was being transferred. Sorry can't help any further.
May 17, 2012 at 7:53 am
Hi,
I faced the same issue , on analysing I found that Article setting for "if the object with same name already exists" was to "Truncate table and reload data" . I modified it to "Delete the object and re-create" then it worked fine.
Solution :
Modified Article properties for the table
Property : Action if name in use
Earlier Value : Truncate All data in the existing object
Fixed value : Drop Existing object and create a new one
Regards,
Avi
February 11, 2015 at 11:25 am
I realize this is a really old post, and I hope everyone has moved on to more current flavors of SQL Server, but I just started a new gig, and I'm supporting SQLServer 2000. I found another work around to this issue. The KB article mentioned above is not clear on how to remove the replicating of non-clustered indexes. The way I was able to do this and get around the statistics error was to check the "Include declared referential integrity" box in the snapshot tab for Article Properties. Once I check this box, I unchecked Clustered and Nonclustered indexes boxes. Reran the snap and viola I didn't get the dreaded
"There are already statistics on table 'table name' named 'stats name'.
(Source: server name (Data source); Error number: 1927)
error.
Hope this helps some other poor soul out there supporting SQL Server 2000 Transactional Replication to later versions of SQL Server.
fjc
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply