SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"There are already statistics on table" error 1927 - can not resolve


"There are already statistics on table" error 1927 - can not resolve

Author
Message
Brian Jones-401554
Brian Jones-401554
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 296

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


Antares686
Antares686
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: Moderators
Points: 27612 Visits: 803

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.





Brian Jones-401554
Brian Jones-401554
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 296

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.


Ameena Lalani-347953
Ameena Lalani-347953
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 65
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.
Brian Jones-401554
Brian Jones-401554
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 296
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.
Ameena Lalani-347953
Ameena Lalani-347953
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 65
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
Brian Jones-401554
Brian Jones-401554
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 296
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.
Avijeet
Avijeet
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 156
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
fjcdba
fjcdba
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 43
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search