MS SQL Server Replication - Re: Initial Snapshot table was too big, agent failed... ? Anyone k

Author

Re: Initial Snapshot table was too big, agent failed... ? Anyone k

Karthik

2005-07-19, 3:23 am

Hi JJ,

I did have a situation like this earlier. I adopted a work around as below

I identified which table's data was the one that was creating the problem.
Generally it is the largest table with lots of data.

I transfered all this data to a temporary table. And I deleted all the data
in the main table.

Now I generated the snapshot and synchronized the server. It went through
without any problem.

After this i used to insert 1000 records from the temp table to the main
table and synchronize. I did this till all the data from the temp table where
moved to the primary table.

I know its a pain to do this but considering the kind of connection I had
and the restrictions imposed, I had to follow this way.

Please note that my table did not have any relationships with other table.
In case your table has relations with some other table you will have to
remove it before deleting the records.

I would advise you to do this over non production hours and with a large
window.

Hope this helps.

Regards,
Karthik

"JJ" wrote:

> Interesting info, thanks Paul.
>
> I won't be able to manually transfer a zipped snapshot to the
> subscriber, unfortunately, but maybe you can offer some more insight.
> This particular publication is actually one-way, from the subscribers
> to the publisher, with no chance of conflicts, so this snapshot is a
> real pain anyhow. I feel that replication is still the right solution
> though.
>
> I was thinking that, to avoid including all of this binary data in the
> snapshot, that I would exclude that table from the publication, and
> instead, include an empty staging table that would use triggers to copy
> the upstream replicated data to the binary table. It's all new data
> with no chance of conflicts, so as long as the setup would work, it
> would work for my application pretty well. Your thoughts?
>
> Thanks,
> Joe
>
>

Paul Ibison

2005-07-19, 11:23 am

In this case I'd definitely go down the nosync route. This article is based
on transactional, but the principle is the same:
http://www.replicationanswers.com/ ...on
s.asp. It'll prevent
the snapshot needing to be implemented (although the metadatas in merge will
still be created).
Cheers,
Paul Ibison, SQL Server MVP

Paul Ibison

2005-07-19, 1:23 pm

Perhaps the snapshot files were obsolete? Anyway, on synchronization you
should find that there is no actual flow of data - just the metadata tables
to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul Ibison

2005-07-20, 3:23 am

Sean,
I agree it does, but only if there isn't a current snapshot available (apart
from a few exceptions like some schema changes). I also agree that this
seems unnecessary if you're not using anonymous subscribers.
Cheers,
Paul Ibison

Vijay

2005-07-24, 3:23 am

Paul,

I am using Transactional replication.

While creating annonymous pull subscription, "No, the subscriber already has the schema and data" check box is disabled.

Also, i would like to create the subscription through sql script(sp_addpullsub
scription ). How can i choose the above option?

Thanks,
Vijay

Create a new subscription to your publication. Select not to deliver the data and schema.

"Paul Ibison" <Paul.Ibison@pygmalion.com> wrote in message news:ere4cUHjFHA.3568@tk2msftngp13.phx.gbl...
> In this case I'd definitely go down the nosync route. This article is based
> on transactional, but the principle is the same:
> http://www.replicationanswers.com/ ...on
s.asp. It'll prevent
> the snapshot needing to be implemented (although the metadatas in merge will
> still be created).
> Cheers,
> Paul Ibison, SQL Server MVP
>
>

Sponsored Links