Will leaving these indexes there cause any
>problems?
No.
I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
Correct
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
Dont know.
2 outta 3 aint bad. ;-)
>--Original Message--
>first: forgive me, is it indices or indexes?
>I had previously asked about adding indexes to tables
that are a part of
>merge replication (sql 2000 servers). Paul Ibison
reccomended checking out
>sp_addscriptexec. I honestly have not done that yet as I
was just doing a
>little initial digging on the subject at that point. I
have a couple other
>questions surrounding this issue:
>1) I had someone add some indexes to replicated tables on
the subcriber end
>of a merge replication scheme by using the usual
Enterprise Manager GUI
>method. They did not replicate to the publisher. I assume
that is normal. I
>actually did not think EM would have let the change be
done since it was
>done on a replication article. Will leaving these indexes
there cause any
>problems? I don't actually need them on the publisher end
right now anyway.
>2) I see that by default indexes are included when a new
snapshot is applied
>to a subscriber. So, if re-applying a new snapshot is a
doable solution, I
>could add indexes to the publisher via the normal EM gui
method and just
>re-apply the new snapshot to get this new index to
subscribers... Correct?
>3) using sp_addscriptexec can be used to add indexes to
a published article
>*without* having to re-apply a new snapshot to
subscribers. Just like
>sp_replAddColumn adds columns.. Correct?
>any info is greatly appreciated. Thanks!
>
>.
>
2 out of 3 ain't bad at all... Thanks! ;)
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6801c4a19e$1ef9d250$a401280a@.phx.gbl...[vbcol=seagreen]
> Will leaving these indexes there cause any
> No.
> I
> method and just
> subscribers... Correct?
> Correct
>
> a published article
> subscribers. Just like
>
> Dont know.
> 2 outta 3 aint bad. ;-)
>
> that are a part of
> reccomended checking out
> was just doing a
> have a couple other
> the subcriber end
> Enterprise Manager GUI
> that is normal. I
> done since it was
> there cause any
> right now anyway.
> snapshot is applied
> doable solution, I
> method and just
> subscribers... Correct?
> a published article
> subscribers. Just like
|||I agree with Chris and (3) yes you can use sp_addscriptexec to send over the
create index statements - or use EM, or scripts etc.
The reason sp_addscriptexec is particularly useful is if you have a lot of
subscribers or if your subscribers are pull subscriptions - you can be sure
everyone gets the change. Be sure to try the script on the publisher first.
A recent poster found that his distribution agent failed due to an incorrect
script. If this happens, then you'll need to edit the script in the repldata
share.
BTW for (2) This is usually a lot less work than recreating a snapshot and
synchronizing.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uYFfuGaoEHA.2864@.TK2MSFTNGP12.phx.gbl...
> I agree with Chris and (3) yes you can use sp_addscriptexec to send over
the
> create index statements - or use EM, or scripts etc.
> The reason sp_addscriptexec is particularly useful is if you have a lot of
> subscribers or if your subscribers are pull subscriptions - you can be
sure
> everyone gets the change. Be sure to try the script on the publisher
first.
> A recent poster found that his distribution agent failed due to an
incorrect
> script. If this happens, then you'll need to edit the script in the
repldata
> share.
> BTW for (2) This is usually a lot less work than recreating a snapshot and
> synchronizing.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Showing posts with label publisher. Show all posts
Showing posts with label publisher. Show all posts
Friday, March 23, 2012
Friday, March 9, 2012
Indexes stop working
We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.
Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.
Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:
CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO
The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.
select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'
I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.
There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.
Help!|||You will want to leave Auto-stats on.
How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?
Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.
Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:
CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO
The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.
select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'
I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.
There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.
Help!|||You will want to leave Auto-stats on.
How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?
Wednesday, March 7, 2012
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)
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)
Subscribe to:
Posts (Atom)