indexing/backup issue with large table - transactional replication  
Author Message
chazo





PostPosted: Tue Aug 03 13:45:13 CDT 2004 Top

SQL Server >> indexing/backup issue with large table - transactional replication

Hello,


I have a table on a database that contains over 6 million rows. There
is one clusted and two non-clustered indexes on the table.

Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
Machine varchar (60) NULL,
LogEntry varchar (1000)NULL,
Active varchar (50) NULL,
SysInfo varchar (255),
Idle varchar (50) NULL,
IP varchar (15) NULL,
KioskDate datetime NULL,
KioskTime varchar (22) NULL,
ServerDate datetime NULL,
ServerTime datetime NULL,
Application varchar (15)NULL,
WebDomain varchar (50)NULL ,
NSCode varchar (10) NULL


pk_1 Clustered on Counter
pk_2 NC on NSCode
pk_3 Machine, KioskDate, KioskTime, NSCode

These indexes work well.

A trans log backup runs nightly apart from sunday morning when a full
backup runs. The table is also part of a transactional replication
subscription (along with two other tables). The problem occurs when
replication fails as the database is being fully backed up at the
weekend. The replication also fails when I try to run DBCC REINDEX on
the table. I have tried to remove the table from the subscription
then run the DBCC command, however this also doesn't work. I have
used DBCC INDEXDEFRAG (successfully) however would my indexes still be
effective as using DBCCREINDEX?

The solution is to ensure the table is backed up, the indexes do not
become ineffective and replication continues to work.

All ideas greatly appreciated!


Thanks


Scott

SQL Server159  
 
 
James





PostPosted: Tue Aug 03 13:45:13 CDT 2004 Top

SQL Server >> indexing/backup issue with large table - transactional replication As far as the INDEXDEFRAG, it should *eventually* be as
effective as a full reindex. Because it essentially works
with smaller pieces of the index, it takes much longer to
run.

What I would highly recommend is using filegroups and
splitting off the non-clustered indexes onto a separate
disk array. That should speed up the performance of a
full reindex. I'd also reindex each index as a separate
step, rather than all of the indexes in one job.

I'd also recommend splitting the full backup into backing
up individual files or filegroups more frequently rather
than a single full backup.

Implementing something like SQL Litespeed can drastically
increase the speed at which a backup finishes.

It sounds like you just don't have a large enough
maintenance window (time). In the end, you have two
options -- decrease the maintenance or increase the time
window. Consider the latter as a possibility.

Hope that helps.

>-----Original Message-----
>Hello,
>
>
>I have a table on a database that contains over 6 million
rows. There
>is one clusted and two non-clustered indexes on the table.
>
>Counter decimal(18, 0) IDENTITY (1, 1) NOT NULL,
>Machine varchar (60) NULL,
>LogEntry varchar (1000)NULL,
>Active varchar (50) NULL,
>SysInfo varchar (255),
>Idle varchar (50) NULL,
>IP varchar (15) NULL,
>KioskDate datetime NULL,
>KioskTime varchar (22) NULL,
>ServerDate datetime NULL,
>ServerTime datetime NULL,
>Application varchar (15)NULL,
>WebDomain varchar (50)NULL ,
>NSCode varchar (10) NULL
>
>
>pk_1 Clustered on Counter
>pk_2 NC on NSCode
>pk_3 Machine, KioskDate, KioskTime, NSCode
>
>These indexes work well.
>
>A trans log backup runs nightly apart from sunday morning
when a full
>backup runs. The table is also part of a transactional
replication
>subscription (along with two other tables). The problem
occurs when
>replication fails as the database is being fully backed
up at the
>weekend. The replication also fails when I try to run
DBCC REINDEX on
>the table. I have tried to remove the table from the
subscription
>then run the DBCC command, however this also doesn't
work. I have
>used DBCC INDEXDEFRAG (successfully) however would my
indexes still be
>effective as using DBCCREINDEX?
>
>The solution is to ensure the table is backed up, the
indexes do not
>become ineffective and replication continues to work.
>
>All ideas greatly appreciated!
>
>
>Thanks
>
>
>Scott
>.
>