Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

Add column to Merge publication causes re-init without subcriberchanges

First off, we have added columns in the past and I understand that
causes a reinit, but the option is in place to upload subscriber
changes. This has worked before, we are now on CU4 and I am
suspecting something might have changed?
It is a vague error message "The merge process could not enumerate
changes at the 'Subscriber'. "
I have tried it both from the GUI and from sp_mergearticlecolumn,
neither worked.
Anyone have any ideas?
fyi, these are CE subscribers...
That's a hard one. Try setting the querytimeout to a large value and
enable logging to see where it is getting stuck. You can specify a
profile name in the sqlcereplication class.
On Nov 15, 12:25 pm, Greg J <greg.judk...@.gmail.com> wrote:
> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
|||Sounds like you are doing something wrong.
First, adding a column does not cause a new snapshot to be created. A new
table yes, a new column - no.
Use:
sp_repladdcolumn @.source_object = 'tableName'
, @.column = 'newColumn'
, @.typetext = 'dataType'
, @.publication_to_add = 'mergePubName'
, @.force_invalidate_snapshot = 0
Second - increase the QueryTimeout value in your merge agent profile.
Finally, make sure you are performing merge systable maintenance... at a
minimum you should be rebuilding indexes on these tables:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||fyi... sp_repladdcolumn is SQL2000 syntax
SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
ChrisB
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> fyi... sp_repladdcolumn is SQL2000 syntax
> SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
> ChrisB
>
> "Greg J" wrote:
>
>
> - Show quoted text -
Yes this is 2005.
I am using the alter table followed by the sp below. We have
@.replicate_ddl turned off, we don't want all columns published.
ALTER TABLE myTable ADD
myColumn smallint NULL
GO
exec sp_mergearticlecolumn @.publication = N'myPub',
@.article = N'myTable',
@.column = N'myColumn,
@.operation = N'add',
@.force_invalidate_snapshot = 1, -- I have tried running without this
option, it does not allow it because there is already a valid
snapshot.
@.force_reinit_subscription = 1 -- I have tried this without the
option, it won't allow it b/c subscribers are already tied to it.
I'm working on getting verbose logging going now...
|||On Nov 15, 1:41 pm, Greg J <greg.judk...@.gmail.com> wrote:
> On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> Yes this is 2005.
> I am using the alter table followed by the sp below. We have
> @.replicate_ddl turned off, we don't want all columns published.
> ALTER TABLE myTable ADD
> myColumn smallint NULL
> GO
> exec sp_mergearticlecolumn @.publication = N'myPub',
> @.article = N'myTable',
> @.column = N'myColumn,
> @.operation = N'add',
> @.force_invalidate_snapshot = 1, -- I have tried running without this
> option, it does not allow it because there is already a valid
> snapshot.
> @.force_reinit_subscription = 1 -- I have tried this without the
> option, it won't allow it b/c subscribers are already tied to it.
> I'm working on getting verbose logging going now...- Hide quoted text -
> - Show quoted text -
Ok, so the lesson to be learned is just do it the way Microsoft
expects you to. I turned on the @.replicate_ddl to true for the
publication and added the column. It put the column in the
publication, brought the schema to all the subscribers, uploaded all
changes, not even a need for a snapshot or re-inits from the devices.
That leaves the question why doesn't the above stored proc behave like
the @.replicate_ddl option would, but I'm ok not knowing.

Activity in one DB causes time-outs in another

We have several SQL 2000 databases on one server.
One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.

We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources.
We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.personally, I would look at optimizing your hour long batch process if at all possible. If you follow the directions in Brett's sticky at the top, I am sure some one would be happy to look over your code for you.|||There's that,yes. Right now it's cursor driven, because, "you know, if we just make some changes to the previous version we'll be done quicker."

Data is read from a file (created at a specific point in time) into a single table, and the batch process consists of two stages: stage one is comparing the new data to what we already have, and create an entry in the log table for every difference. Stage two is changing our data so that it reflects the data in the file, for that particular point in time.

I'm pretty sure that stage one can be done set-based, but I'm not so sure about stage two. The actions required for each row in the new data depend on what's already there in our db.