Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / September 2006

Tip: Looking for answers? Try searching our database.

SQL 2000 transactional replication is generating snapshot every hour

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Roberto T. - 28 Sep 2006 10:55 GMT
I have three SQL 2000 servers, with transactional replication between them
(27GB database, +2GB/month)
SQL1 -> SQL2 -> SQL3
SQL1 is a publisher+distributor
SQL2 subcribed to SQL1, and publisher+distributor for SQL3
SQL3 subscribed to SQL2

Everything is working fine, but in the last week the distribution agents
(run at the publicator) die with timeout very frequentlly (once-twice a day)
I have noticed that when this happens it's at every hour (XX:00 - XX:05),
and at the same time SQL2 is generating a snapshot for every publication it
has. This doesn't happen in SQL1, as it detects in some publications that no
sucriptions nedded reinitialization, but SQL2 is generating a snapshot
(aprox. 2GB) every hour for every publication. SQL1 does the same, but only
for some publication with little articles.

I have disabled the snapshot agents in SQL2 as an temporary solution. Does
anybody know why SQL is generating the snapshot every hour? Where can I find
information about the publication/snapshot?
I have tried to reinitialize a subscription, but once reinitialized the
snapshot is generated again and again.

Thanks in advance, best regards,
Roberto T.
Paul Ibison - 28 Sep 2006 12:58 GMT
Roberto,
the snapshot files are created for anonymous subscribers and for subscribers
which need reinitializing. I usually disable the snapshot job to prevent
unnecessary snapshots being created and only run it manually when a
reinitialization is required, which is what I assume you have done.
    Cheers,
         Paul Ibison SQL Server MVP, www.replicationanswers.com .
Roberto T. - 28 Sep 2006 15:06 GMT
Thank you Paul, that was the reason. Some publications were marked to allow
anonymous subscriptions and only those publications were generating the
snapshot continously. I can't find how to change it in a running
publication, so I'll disable those snapshot jobs as a solution.

Kind regards,
Roberto

> Roberto,
> the snapshot files are created for anonymous subscribers and for subscribers
[quoted text clipped - 3 lines]
>      Cheers,
>           Paul Ibison SQL Server MVP, www.replicationanswers.com .
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.