Re: Log won't Truncate when Publication is created
- From: Bodhicitta <Bodhicitta@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 13 Feb 2007 16:03:05 -0800
Well, kind of good news - I can't reproduce the problem any more. That means
that it is fixed. The only thing I did differently was that the last time I
deleted the publication, I first deleted the subscription. When I deleted
the subscription, I was prompted to delete the subscription at the subscriber
and I chose yes - to delete it. Could an improperly configured subscription
cause this behavior?
Just to recap. Previously, when attempting to fix the growing log file, I
deleted the publication, not the subscription. This fixed it. However, if I
recreated the publication and then recreated the subscription, the problem
came right back. If I was not deleting the subscription, then when I
attempted to recreate the subscription with the same name, what happened to
the old subscription? Maybe it persisted with some bad configuration info
which prevented the log file from truncating???
Thanks again for the help.
"Hilary Cotter" wrote:
I don't believe a cursor (which isn't always a bad thing) will cause any.
problems like this. issue dbcc opentran to see if that shows anything.
--
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:4C48EEFD-54DB-4912-B6F8-BF66EBEC669D@xxxxxxxxxxxxxxxx
I will try that, thanks Hilary. I have been doing more testing and have
figured out that there is one stored procedure which seems to be
preventing
the log from truncating. Meaning, as soon as the sproc runs the log
starts
growing, even after the sproc completes successfully, but only if a
publication exists. The sproc is using a cursor, unfortunately a
necessary
cursor, to insert rows into a table. Do you know of any reason that a
cursor
would prevent the logs from truncating when a snapshot publication exists?
Thanks again for your help.
"Hilary Cotter" wrote:
Next time this happens issue a repldone (sp_repldone - its documented in
bol) to see if this helps.
--
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:44D3DB29-9F2B-4F2C-8AF3-58F35240698A@xxxxxxxxxxxxxxxx
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.
- References:
- Re: Log won't Truncate when Publication is created
- From: Hilary Cotter
- Re: Log won't Truncate when Publication is created
- From: Bodhicitta
- Re: Log won't Truncate when Publication is created
- From: Hilary Cotter
- Re: Log won't Truncate when Publication is created
- From: Bodhicitta
- Re: Log won't Truncate when Publication is created
- From: Hilary Cotter
- Re: Log won't Truncate when Publication is created
- Prev by Date: Re: Configuring Distributor
- Next by Date: Re: Configuring Distributor
- Previous by thread: Re: Log won't Truncate when Publication is created
- Next by thread: Re: Configuring Distributor
- Index(es):
Relevant Pages
|
