Indexing on large table kills Transactional Replication

Hi,
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>

indexing for replicated tables..

Hi All,
We are having a lot of tables in an online
database 'TRANS'. There are no clustered indexes in the
tables. There are only non-clustered indexes in the
tables. we do a weekly re-org of tables by creating and
dropping non-clustered indexes.
We are planning to configure transactional replication for
this database. For tables having non-clustered indexes
alone - dbcc dbreindex does not improve scan density by
much!
So, the stratergy of creating and dropping clustered
indexes work? If not, what is the best startegy to get the
same done?
My next question is,
is it possible to reindex tables in Merge replicated
databases?If your filegroup has more than one file, scan density is not a good
measure, use logical or extent scan fragmentation...
Almost all tables will benefit from a clustered index... perhaps you might
try adding a clustered to several of the tables and see if that helps...
Regarding Merge replication, you may re-do indexes at the client if you
wish... make sure you always have the unique index on the GUID...
"Bharath" <ramakrishnan.bharadhwaj@.citigroup.com> wrote in message
news:069801c33eda$5b27e640$a101280a@.phx.gbl...
> Hi All,
> We are having a lot of tables in an online
> database 'TRANS'. There are no clustered indexes in the
> tables. There are only non-clustered indexes in the
> tables. we do a weekly re-org of tables by creating and
> dropping non-clustered indexes.
> We are planning to configure transactional replication for
> this database. For tables having non-clustered indexes
> alone - dbcc dbreindex does not improve scan density by
> much!
> So, the stratergy of creating and dropping clustered
> indexes work? If not, what is the best startegy to get the
> same done?
> My next question is,
> is it possible to reindex tables in Merge replicated
> databases?

Indexes being replicated

I have set up transactional replication between two SQL Server 2000 boxes. I
am replicating a table. On the publisher this table has a primary key, a
clustered index, number of non clustered indexes and a check constraint.
I want to replicate only the table structure and data, but none of the other
objects associated with the table.
When I use Enterprise Manager to set the snapshot options I want to uncheck
"Include declared referential integrity", "Clustered Indexes" and
"Nonclustered indexes" in the "Copy objects to destination" section .
However, the "Nonclustered indexes" checkbox is greyed out and ticked. If I
check "Include declared referential integrity" then I can uncheck
"Nonclustered indexes". So far so good, but then when I uncheck "Include
declared referential integrity" the "Nonclustered indexes" box gets
autmoatically checked and greyed out.
Why is it not possible to uncheck both "Include declared referential
integrity" and "Nonclustered indexes"?
Is there something I'm misunderstanding here?
Thanks
Stephen
Stephen,
only a few of the possible permutations are covered in this dialog box. For
a more fine control, you can set the @.schema_option argument of
sp_addarticle.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

indexes and merge replication

just putting out a feeler on this since I think I am about to be asked to do
this.
Can I add/remove indexes on replicated tables? If so, will that schema
change replicate to subscribers (sql server 2000 subscribers)?
I have used the sp_repladdcolumn stored procedure. Hopefully there is one
for indexes as well?
any info is appreciated. Thanks.
Thanks Paul. I'll checkout sp_addscriptexec in sql server books online.
I appreciate the direction.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d3f01c49a59$3a4fed60$a501280a@.phx.gbl...
> DJC,
> you can use sp_addscriptexec to replicate index changes.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>