Re: Log won't Truncate when Publication is created

I am certain that I am using Snapshot replication. In fact I have been
testing this, trying to figure out what is going on for days, deleting and
recreating the snapshot publication. We are using snapshot since the
database is almost completely rebuilt with each update, and the updates
happen infrequently. The snapshot is used to update a development database
that only needs refreshing once per day. I had this same problem with
another database, which was configured the same, but a reinitialization fixed
the problem. In this case, the only thing that frees up the growing active
log space is to delete the publication.

"Hilary Cotter" wrote:

The log reader is not replicating all the transactions. The log is
"truncated" at the last replication point - or where transactions are read
out of the transaction log and marked as replicated.

Are you certain you are using snapshot replication? Normally you get this
with transactional replication.

I would also use transactional replication as it means (in most situations)
that less data is going across the wire.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Bodhicitta" <Bodhicitta@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3923DF8C-3BC1-4D25-A7C5-03CA22DCC106@xxxxxxxxxxxxxxxx
We are having an issue whereby the log file on a database will not
truncate
once a snapshot publication is created for the database.

-The database is set to the Simple Recovery Model.
-Data in the database is updated/refreshed every 3 hours via a series of
stored procedures which complete in about 25 minutes.
- As soon as a snapshot publication and push subscription are created,
with
each pass of the stored procedures (every 3 hours) which update the
database,
the active portion of the log grows, eventually causing the log file to
autogrow, and eventually we run out of disk space.
- The publication is scheduled to create a snapshot once per day, and the
subscriber is scheduled to synchronize with the snapshot thereafter.
-The database is fully backed up once per day, about an hour after the
snapshot is created.
-The stored procedures are not running while either the snapshot or backup
are in progress. When no publications are present, the log file stays
small
due to the Simple Recovery Model.
-The log truncates as soon as I delete the publication.

What is causing the log to grow like this? Thanks.



.

Relevant Pages

  • Re: Log wont Truncate when Publication is created
    ... would prevent the logs from truncating when a snapshot publication exists? ... Looking for a SQL Server replication book? ... recreating the snapshot publication. ... database is almost completely rebuilt with each update, ...
    (microsoft.public.sqlserver.replication)
  • Re: Log wont Truncate when Publication is created
    ... Looking for a SQL Server replication book? ... recreating the snapshot publication. ... database is almost completely rebuilt with each update, ... log space is to delete the publication. ...
    (microsoft.public.sqlserver.replication)
  • Re: Prefetch objects failed Error when added more than 2 table as
    ... snapshoting tables and then came the line:The replication agent had ... seeing (Prefetch objects failed for Database 'myDB'), ... snapshot agent from the command line with -OutputVerboseLevel of 2. ...
    (microsoft.public.sqlserver.replication)
  • Re: Merge Replication - Deleting Data
    ... go back and check (peek in the database) to make sure it hasn't happened ... My whole replication strategy is managed using DMO. ... Filtering on the publication. ... > and the order of the tables in the snapshot. ...
    (microsoft.public.sqlserver.replication)
  • Re: Log wont Truncate when Publication is created
    ... deleted the publication, I first deleted the subscription. ... recreating the snapshot publication. ... database is almost completely rebuilt with each update, ...
    (microsoft.public.sqlserver.replication)
Loading