May 5, 2003 at 12:29 pm
Dear Friends,
I have been struggling with the following
problem for a while and would appreciate
any suggestions or comments.
Scenario:
We have two redundant database servers on our system (running SQL Server
2000) each of which have a SQL Server database called ‘myDB’. They stay
in sync by using push transactional replication from the primary (publisher)
to the backup (subscriber). Clients transact only with the primary.
Problem :
When a failure occurs on the backup server, we stop the replication
process and cleanup all replication information. This drops the articles,
publications, subscriptions, terminates logreader jobs,
drops the distribution db and also disables the server
as a distributor. As a result we should be clear of ALL
information about replication from the server. However, if a client
application was transacting with the primary while replication is
going on and continues to do so during the failure, this seems to
result in unreplicated transactions in the transaction log as indicated
by the DBCC Opentran query and sp_repltrans. From then on, the transaction log will grow
till it completely fills up all allocated space. The db is set to auto
truncate but cannot do so because of the stuck transactions.
The log can be cleared ONLY if the client application is shutdown
and then executing the sp_repldone stored proc to mark all transactions
as done.
Note that we attempt to execute this SP as part of our process to stop
and clean replication but apparently it does not do its job.
It is as if the active client connection somehow prevents the log from
being cleared of unreplicated transactions.
We have tried executing sp_flush, sp_repldone, backup log with
truncate_only using Query Analyser. The backup log call will force
truncate the log but will not clear the unreplicated transaction.
If the client application closes the db connection, executing sp_repldone
succeeds and the log will then be able to auto truncate since the
unreplicated transaction is cleared.
What is preventing sp_repldone from succeeding while the client has an
active connection ?
- CD
May 5, 2003 at 2:49 pm
Why don't you try after backing up the log, dbcc shrinkfile and see if that works.
May 5, 2003 at 4:06 pm
Not an answer, but I think you're doing more work than you need to. If a pub gets out of sync all you need to is reinialize it, then re-run the snapshot agent to push a new copy to the subscriber.
Andy
May 5, 2003 at 5:25 pm
We already did that but it does not seem to make a difference in the state of the non-replicated transactions.
- CD
quote:
Why don't you try after backing up the log, dbcc shrinkfile and see if that works.
May 5, 2003 at 5:51 pm
Andy,
Thank you for your reply. There is no guarantee if or when the backup server
will be reattached. Therefore, I must completelly stop replication otherwise our transaction log file will reach its maximum allocated size and operations will stop.
what makes me confused is that the transaction log continues to grow with transactions marked for replication even though I had stopped replication, as verified by using sp_repltrans. Using sp_replflush or sp_repldone does not make a difference. If I close the client application, then sp_replflush/sp_repldone do the job. Unfortunately, I cannot close the client application.
Could you give some insight on what is making the updates from the client application to be marked as for replication?
I suspect that there is some replication information somehow lingering on the database.
Thanks
CD
May 5, 2003 at 6:16 pm
Transactions will only be held in the log if there is one or more active subscriptions to a publication. Transactions only stay in the log until the log reader processes them, at which point they are stored in the distribution db and cleared for truncation in the log. Once a subscription expires a job runs to clean up unposted transactions in the distribution db. Are you running the log reader continuously? Have the option set to require a log backup before log reader can clear the transaction?
I use transactional a lot, almost zero problems with it.
Andy
May 6, 2003 at 2:23 pm
Andy,
We keep the log reader running all the time while replication is going on, except when we shut down replication. Our sequence of steps to clean-up replication are:
1. clean-up the subscription,
2. clean-up publication,
3. clean-up distribution
4. perform sp_replflush/sp_repldone
5. Stop/start the SQL Server agent.
6. clean-up pending jobs
7. drop distribution database
I believe that the log reader is stopped when we stop the agent.
Do you see any problem if transactions are performed between steps 4 and 5? Also, Is there a way to stop the log reader from posting transactions to the log?
As far as the option so that the log reader requires a backup log before clearing the transaction, I am not sure. I do not explicitly set that option but I think it must not be set because, under normal operation, I do not perform periodic backup logs and yet the transaction logs are auto-shrunk.
Thank you
- CD
May 6, 2003 at 2:55 pm
No issues with transactions issued once all subscriptions are dropped. My point is still that you're doing a LOT more work than you need to just because a pub got out of sync.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply