August 25, 2008 at 10:29 am
The log files on a subscriber only node in a peer to peer transactional replication continues to grow despite all efforts otherwise. Following are observations:
1. We set up a subscriber only node in a peer to peer transactional replication by creating the database file from a backup and then initializing the subscription from the backup:
--Execute on the Primary Server the one that was backed up
--Replace |DATABASE| with name of the target database e.g.. LIMS
--Replace |PEER_SERVER| with name of the OTHER Server
--Replace |USERNAME| username for the WINDOWS account under which the agent runs (this could be your service account) e.g. svc_sqlsvr
--Replace |PASSWORD| password for above
--REPLACE |PUBLICATION| with the name of the publication
--Replace |BACKUPPATH| with the path to the file that was restored on the Peer e.g. E:\LIMS.BAK
--Replace |DOMAIN|
--Add subscription
USE [|DATABASE|]
EXEC sp_addsubscription
@publication = N'|PUBLICATION|', @subscriber = N'|PEER_SERVER|',
@destination_db = N'|DATABASE|', @subscription_type = N'Push',
@sync_type = N'initialize with backup', @article = N'all',
@update_mode = N'read only', @subscriber_type = 0,
@backupdevicetype = 'disk', @backupdevicename = '|BACKUPPATH|'
GO
--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent
@publication = N'|PUBLICATION|', @subscriber = N'|PEER_SERVER|',
@subscriber_db = N'|DATABASE|', @job_login = N'|DOMAIN|\|USERNAME|',
@job_password = N'|PASSWORD|', @subscriber_security_mode = 1,
@frequency_type = 64, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 4, @frequency_subday_interval = 5,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0,
@dts_package_location = N'Distributor'
GO
2. As soon as the push subscription agent is added the problems begin:
The TSQL command "DBCC SQLPERF(logspace)" indicates that the Log was completely full (99% Used) with transactions even after a log backup, which was supposed to result in freeing space within the Log.
3. The TSQL command "DBCC OPENTRAN" indicates that the Replication is mysteriously maintaining and open transaction. In my mind this should not be. This is a subscriber only node, a listener only
Transaction information for database 'LIMS'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (851710:2892:246)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
3. A query against sys.databases indicates that the log file is locked by replication:
name Log_reuse_wait log_reuse_wait_desc
master 0 NOTHING
tempdb 0 NOTHING
model 2 LOG_BACKUP
msdb 0 NOTHING
LIMS 6 REPLICATION
I have tried everything to fix the issue including:
1. Backup Log and Shrink
2. Add and remove publication followed by #1.
3. Trying to edit the System tables directly (not so easy in SQL 2005), but I couldn't figure out what to update.
I now believe this is a Microsoft bug and need to get some guidance from them on how to fix the problem.
August 25, 2008 at 10:46 am
Did you try dropping the Push subscription and using a Pull instead? Is that feasible?
August 25, 2008 at 11:05 am
I did not try this however the distribution agent is already running on the source servers to service other nodes in the replication. We do not have a separate distribution server. My thought was to keep the subscriber a passive listener and have the Publishers (there are more than one) do the work
August 26, 2008 at 8:44 am
I believe that you can leave that configuration alone. As long as you allow for a pull subscription, you can let the subscriber pull the data down to it. Then you can see if you get different log behavior. Otherwise, I'd have to suggest that there's an 'incomplete' transaction preventing log space from freeing up. Try searching the forums on that topic - I know there are other posts addressing it in more detail.
[edited because I got confused which thread I was answering....not enough coffee]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply