January 5, 2009 at 11:13 pm
Hi,
There is 249 non clustred index allowed in sql 2000.
on of the database table have 500 column, Trans replication faild while creating snapshots folder, because table have 500 column.
Is there another way to configure the replication.
Anyone could tell me suggestion.
thanks
January 6, 2009 at 12:13 am
Think about composit index.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 6, 2009 at 12:31 am
In addition to the above, I guess Replication do not require index on all the columns. But it require a Cluster Index to get the article replicated.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 6, 2009 at 1:59 am
Thanks for your reply
In Artical properties, i unclick non clustred index, again i have faced the following error
Cannot create more than 249 nonclustered indices or column statistics on one table.(Source: JGSRVR55 (Data source); Error number: 1910)
thanks
January 6, 2009 at 2:00 am
this error while running snapshots agents,
All user table , user has created one rowguid (uniqueidentifier) for all tables. is there problem for while running snapshots agents.
January 6, 2009 at 6:21 am
Why do you have 249 or so indexes on a table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 6:31 am
free_mascot (1/6/2009)
In addition to the above, I guess Replication do not require index on all the columns. But it require a Cluster Index to get the article replicated.
Transactional replication requires a primary key. It doesn't have to be a clustered index though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 10:35 pm
Thanks for all yours reply..
Actually what is the problem that replication? we are not create index on the databasel
One of the client people installed s/w in production envorinment. designed database, developed application and all. so this database contain 78 tables some table have 500 column and all the table having primary key and rowguid coulmn, we want to configure the replication this database.
Transactional replication also has been done but snapshot agent failure while generating snapshot of the folder at default unc location.
Error is - can not create index more than 249
thanks
January 7, 2009 at 12:01 am
Replication does not create indexes.
Please run the following for the table in question. What does it return?
SELECT object_name(id), name, INDEXPROPERTY(id, name, 'IsStatistics') FROM sysindexes
WHERE id = object_ID('Table Name')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 1:20 am
Hi Gail thanks for reply..
Table name is KW_1, i got the result after running your below statement
SELECT object_name(id), name, INDEXPROPERTY(id, name, 'IsStatistics') FROM sysindexes
WHERE id = object_ID('KW_1')
KW_1PK_KW_10
KW_1_WA_Sys_field1_6EF57B661
KW_1_WA_Sys_field2_6EF57B661
KW_1_WA_Sys_field3_6EF57B661
KW_1_WA_Sys_field4_6EF57B661
KW_1_WA_Sys_field5_6EF57B661
KW_1_WA_Sys_field6_6EF57B661
KW_1_WA_Sys_field7_6EF57B661
KW_1_WA_Sys_field8_6EF57B661
KW_1_WA_Sys_field9_6EF57B661
KW_1_WA_Sys_field10_6EF57B661
KW_1_WA_Sys_field11_6EF57B661
KW_1_WA_Sys_field12_6EF57B661
KW_1_WA_Sys_field13_6EF57B661
KW_1_WA_Sys_field14_6EF57B661
KW_1_WA_Sys_field15_6EF57B661
KW_1_WA_Sys_field16_6EF57B661
KW_1_WA_Sys_field17_6EF57B661
KW_1_WA_Sys_field18_6EF57B661
KW_1_WA_Sys_field19_6EF57B661
KW_1_WA_Sys_field20_6EF57B661
KW_1_WA_Sys_field21_6EF57B661
KW_1_WA_Sys_field22_6EF57B661
KW_1_WA_Sys_field23_6EF57B661
KW_1_WA_Sys_field24_6EF57B661
KW_1_WA_Sys_field25_6EF57B661
KW_1_WA_Sys_field26_6EF57B661
KW_1_WA_Sys_field27_6EF57B661
KW_1_WA_Sys_field28_6EF57B661
KW_1_WA_Sys_field29_6EF57B661
KW_1_WA_Sys_field30_6EF57B661
KW_1_WA_Sys_field31_6EF57B661
KW_1_WA_Sys_field32_6EF57B661
KW_1_WA_Sys_field33_6EF57B661
KW_1_WA_Sys_field34_6EF57B661
KW_1_WA_Sys_field35_6EF57B661
KW_1_WA_Sys_field36_6EF57B661
KW_1_WA_Sys_field37_6EF57B661
KW_1_WA_Sys_field38_6EF57B661
KW_1_WA_Sys_field39_6EF57B661
KW_1_WA_Sys_field40_6EF57B661
KW_1_WA_Sys_field41_6EF57B661
KW_1_WA_Sys_field42_6EF57B661
KW_1_WA_Sys_field43_6EF57B661
KW_1_WA_Sys_field44_6EF57B661
KW_1_WA_Sys_field45_6EF57B661
KW_1_WA_Sys_field46_6EF57B661
KW_1_WA_Sys_field47_6EF57B661
KW_1_WA_Sys_field48_6EF57B661
KW_1_WA_Sys_field49_6EF57B661
KW_1_WA_Sys_field50_6EF57B661
KW_1_WA_Sys_field51_6EF57B661
KW_1_WA_Sys_field52_6EF57B661
KW_1_WA_Sys_field53_6EF57B661
KW_1_WA_Sys_field54_6EF57B661
KW_1_WA_Sys_field55_6EF57B661
KW_1_WA_Sys_field56_6EF57B661
KW_1_WA_Sys_field57_6EF57B661
KW_1_WA_Sys_field58_6EF57B661
KW_1_WA_Sys_field59_6EF57B661
KW_1_WA_Sys_field60_6EF57B661
KW_1_WA_Sys_field61_6EF57B661
KW_1_WA_Sys_field62_6EF57B661
KW_1_WA_Sys_field63_6EF57B661
KW_1_WA_Sys_field64_6EF57B661
KW_1_WA_Sys_field65_6EF57B661
KW_1_WA_Sys_field66_6EF57B661
KW_1_WA_Sys_field67_6EF57B661
KW_1_WA_Sys_field68_6EF57B661
KW_1_WA_Sys_field69_6EF57B661
KW_1_WA_Sys_field70_6EF57B661
KW_1_WA_Sys_field71_6EF57B661
KW_1_WA_Sys_field72_6EF57B661
KW_1_WA_Sys_field73_6EF57B661
KW_1_WA_Sys_field74_6EF57B661
KW_1_WA_Sys_field75_6EF57B661
KW_1_WA_Sys_field76_6EF57B661
KW_1_WA_Sys_field77_6EF57B661
KW_1_WA_Sys_field78_6EF57B661
KW_1_WA_Sys_field79_6EF57B661
KW_1_WA_Sys_field80_6EF57B661
KW_1_WA_Sys_field81_6EF57B661
KW_1_WA_Sys_field82_6EF57B661
KW_1_WA_Sys_field83_6EF57B661
KW_1_WA_Sys_field84_6EF57B661
KW_1_WA_Sys_field85_6EF57B661
KW_1_WA_Sys_field86_6EF57B661
KW_1_WA_Sys_field87_6EF57B661
KW_1_WA_Sys_field88_6EF57B661
KW_1_WA_Sys_field89_6EF57B661
KW_1_WA_Sys_field90_6EF57B661
KW_1_WA_Sys_field91_6EF57B661
KW_1_WA_Sys_field92_6EF57B661
KW_1_WA_Sys_field93_6EF57B661
KW_1_WA_Sys_field94_6EF57B661
KW_1_WA_Sys_field95_6EF57B661
KW_1_WA_Sys_field96_6EF57B661
KW_1_WA_Sys_field97_6EF57B661
KW_1_WA_Sys_field98_6EF57B661
KW_1_WA_Sys_field99_6EF57B661
KW_1_WA_Sys_field100_6EF57B661
KW_1_WA_Sys_field101_6EF57B661
KW_1_WA_Sys_field102_6EF57B661
KW_1_WA_Sys_field103_6EF57B661
KW_1_WA_Sys_field104_6EF57B661
KW_1_WA_Sys_field105_6EF57B661
KW_1_WA_Sys_field106_6EF57B661
KW_1_WA_Sys_field107_6EF57B661
KW_1_WA_Sys_field108_6EF57B661
KW_1_WA_Sys_field109_6EF57B661
KW_1_WA_Sys_field110_6EF57B661
KW_1_WA_Sys_field111_6EF57B661
KW_1_WA_Sys_field112_6EF57B661
KW_1_WA_Sys_field113_6EF57B661
KW_1_WA_Sys_field114_6EF57B661
KW_1_WA_Sys_field115_6EF57B661
KW_1_WA_Sys_field116_6EF57B661
KW_1_WA_Sys_field117_6EF57B661
KW_1_WA_Sys_field118_6EF57B661
KW_1_WA_Sys_field119_6EF57B661
KW_1_WA_Sys_field120_6EF57B661
KW_1_WA_Sys_field121_6EF57B661
KW_1_WA_Sys_field122_6EF57B661
KW_1_WA_Sys_field123_6EF57B661
KW_1_WA_Sys_field124_6EF57B661
KW_1_WA_Sys_field125_6EF57B661
KW_1_WA_Sys_field126_6EF57B661
KW_1_WA_Sys_field127_6EF57B661
KW_1_WA_Sys_field128_6EF57B661
KW_1_WA_Sys_field129_6EF57B661
KW_1_WA_Sys_field130_6EF57B661
KW_1_WA_Sys_field131_6EF57B661
KW_1_WA_Sys_field132_6EF57B661
KW_1_WA_Sys_field133_6EF57B661
KW_1_WA_Sys_field134_6EF57B661
KW_1_WA_Sys_field135_6EF57B661
KW_1_WA_Sys_field136_6EF57B661
KW_1_WA_Sys_field137_6EF57B661
KW_1_WA_Sys_field138_6EF57B661
KW_1_WA_Sys_field139_6EF57B661
KW_1_WA_Sys_field140_6EF57B661
KW_1_WA_Sys_field141_6EF57B661
KW_1_WA_Sys_field142_6EF57B661
KW_1_WA_Sys_field143_6EF57B661
KW_1_WA_Sys_field144_6EF57B661
KW_1_WA_Sys_field145_6EF57B661
KW_1_WA_Sys_field146_6EF57B661
KW_1_WA_Sys_field147_6EF57B661
KW_1_WA_Sys_field148_6EF57B661
KW_1_WA_Sys_field149_6EF57B661
KW_1_WA_Sys_field150_6EF57B661
KW_1_WA_Sys_field151_6EF57B661
KW_1_WA_Sys_field152_6EF57B661
KW_1_WA_Sys_field153_6EF57B661
KW_1_WA_Sys_field154_6EF57B661
KW_1_WA_Sys_field155_6EF57B661
KW_1_WA_Sys_field156_6EF57B661
KW_1_WA_Sys_field157_6EF57B661
KW_1_WA_Sys_field158_6EF57B661
KW_1_WA_Sys_field159_6EF57B661
KW_1_WA_Sys_field160_6EF57B661
KW_1_WA_Sys_field161_6EF57B661
KW_1_WA_Sys_field162_6EF57B661
KW_1_WA_Sys_field163_6EF57B661
KW_1_WA_Sys_field164_6EF57B661
KW_1_WA_Sys_field165_6EF57B661
KW_1_WA_Sys_field166_6EF57B661
KW_1_WA_Sys_field167_6EF57B661
KW_1_WA_Sys_field168_6EF57B661
KW_1_WA_Sys_field169_6EF57B661
KW_1_WA_Sys_field170_6EF57B661
KW_1_WA_Sys_field171_6EF57B661
KW_1_WA_Sys_field172_6EF57B661
KW_1_WA_Sys_field173_6EF57B661
KW_1_WA_Sys_field174_6EF57B661
KW_1_WA_Sys_field175_6EF57B661
KW_1_WA_Sys_field176_6EF57B661
KW_1_WA_Sys_field177_6EF57B661
KW_1_WA_Sys_field178_6EF57B661
KW_1_WA_Sys_field179_6EF57B661
KW_1_WA_Sys_field180_6EF57B661
KW_1_WA_Sys_field181_6EF57B661
KW_1_WA_Sys_field182_6EF57B661
KW_1_WA_Sys_field183_6EF57B661
KW_1_WA_Sys_field184_6EF57B661
KW_1_WA_Sys_field185_6EF57B661
KW_1_WA_Sys_field186_6EF57B661
KW_1_WA_Sys_field187_6EF57B661
KW_1_WA_Sys_field188_6EF57B661
KW_1_WA_Sys_field189_6EF57B661
KW_1_WA_Sys_field190_6EF57B661
KW_1_WA_Sys_field191_6EF57B661
KW_1_WA_Sys_field192_6EF57B661
KW_1_WA_Sys_field193_6EF57B661
KW_1_WA_Sys_field194_6EF57B661
KW_1_WA_Sys_field195_6EF57B661
KW_1_WA_Sys_field196_6EF57B661
KW_1_WA_Sys_field197_6EF57B661
KW_1_WA_Sys_field198_6EF57B661
KW_1_WA_Sys_field199_6EF57B661
KW_1_WA_Sys_field200_6EF57B661
KW_1_WA_Sys_field201_6EF57B661
KW_1_WA_Sys_field202_6EF57B661
KW_1_WA_Sys_field203_6EF57B661
KW_1_WA_Sys_field204_6EF57B661
KW_1_WA_Sys_field205_6EF57B661
KW_1_WA_Sys_field206_6EF57B661
KW_1_WA_Sys_field207_6EF57B661
KW_1_WA_Sys_field208_6EF57B661
KW_1_WA_Sys_field209_6EF57B661
KW_1_WA_Sys_field210_6EF57B661
KW_1_WA_Sys_field211_6EF57B661
KW_1_WA_Sys_field212_6EF57B661
KW_1_WA_Sys_field213_6EF57B661
KW_1_WA_Sys_field214_6EF57B661
KW_1_WA_Sys_field215_6EF57B661
KW_1_WA_Sys_field216_6EF57B661
KW_1_WA_Sys_field217_6EF57B661
KW_1_WA_Sys_field218_6EF57B661
KW_1_WA_Sys_field219_6EF57B661
KW_1_WA_Sys_field220_6EF57B661
KW_1_WA_Sys_field221_6EF57B661
KW_1_WA_Sys_field222_6EF57B661
KW_1_WA_Sys_field223_6EF57B661
KW_1_WA_Sys_field224_6EF57B661
KW_1_WA_Sys_field225_6EF57B661
KW_1_WA_Sys_field226_6EF57B661
KW_1_WA_Sys_field227_6EF57B661
KW_1_WA_Sys_field228_6EF57B661
KW_1_WA_Sys_field229_6EF57B661
KW_1_WA_Sys_field230_6EF57B661
KW_1_WA_Sys_field231_6EF57B661
KW_1_WA_Sys_field232_6EF57B661
KW_1_WA_Sys_field233_6EF57B661
KW_1_WA_Sys_field234_6EF57B661
KW_1_WA_Sys_field235_6EF57B661
KW_1_WA_Sys_field236_6EF57B661
KW_1_WA_Sys_field237_6EF57B661
KW_1_WA_Sys_field238_6EF57B661
KW_1_WA_Sys_field239_6EF57B661
KW_1_WA_Sys_field240_6EF57B661
KW_1_WA_Sys_field241_6EF57B661
KW_1_WA_Sys_field242_6EF57B661
KW_1_WA_Sys_field243_6EF57B661
KW_1_WA_Sys_field244_6EF57B661
KW_1_WA_Sys_field245_6EF57B661
KW_1_WA_Sys_field246_6EF57B661
KW_1_WA_Sys_field247_6EF57B661
KW_1_WA_Sys_field248_6EF57B661
KW_1_WA_Sys_field249_6EF57B661
further what i should do?
Thanks
January 7, 2009 at 1:33 am
Thought so.
Those aren't indexes. They're automatically created system statistics and they can be safely dropped if they're causing problems (as they are here). The optimiser will recreate any that it needs.
DROP STATISTICS < Table Name <.< Statistics Name >
You can do that will all of the ones starting with the _WA_SYS name
You should give some thought to putting some indexes on that table. It only has the primary key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 6:04 am
Hi Gail,
Thanks for your valuable reply, i will deleted all system statistices on table. by using this command
Drop statistics kw_1._WA_Sys_field2_6EF57B66
could tell me, why system automatically statistices created this table?
Thank you so mouch.
January 7, 2009 at 6:24 am
It's created by the optimiser when running queries so that it can get info about the distribution of data in the column, to get a better execution plan.
See this post on statistics - http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply