Transactional Push Distribution Agent Slow

Jan 12, 2007

We have quite a lot of Transactional replication, and over the past few weeks it had become quite slow. I spent the last few days trying to resolve the problem and found that a Push subscription was delivering transactions at about 50/sec and a Pull subscription was delivering at around 700/sec.

This test was run on the Same publication, same subscriber so hardware and links are all the same, the only difference was the type of subscription. I've since changed a few of our subscriptions for the larger publications and have found they run quicker than previously.

Has anyone else seen this behavour and is there a valid reason why this would occur. The distributor and subscriber are both running SQL2005 SP1 and the publisher is SQL2000 SP4.

If anyone could shed some light on this it would be appreciated.

View 7 Replies

Reinitializing Push Subscription After It Expired In Transactional Replication

Jan 24, 2007

I am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.

I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.

Is there a fast way of doing it?

-Nipul

View 1 Replies View Related

Transactional Replication Distribution

May 17, 2006

Hi

I use Sql Server 2000 with Service pack and transactional replication for transfering data between Servers and for comunication between Servers i use Dialup but it is a little Slow line.

Ihave a table that need transfer about 4700 records. I update this table and distributer Agent starts. at the Same time Icontrol Transfering by this Select in destination query Analiser:

Select Count(*) from TableName with (nolock)

it counts until about 3700 and retry again from 1 and so on agin..

Please gide me how can i overcome this problem

thank you for kindness

View 1 Replies View Related

SQL Transactional Replication Distribution Server Struggling

Jun 5, 2006

We are attempting to rollout a name and address system to 10,000 users who will use an application connected to an MSDE database.

We are using transactional replication to distribute data updates to them. Clients are connecting via the On-Idle feature of Synchronization Manager to grab transactions.

Server spec:
Network card: 1GB
Processors: 2* Xeon 3.2Ghz
Server spec: DL380 2Gb memory
Concurrent connections set to: 600
Disc: RAID 10 with 6400 controller

We are not using hyper-threading.

So far we have rolled the system out to 3500 subscribers, 500 per day.

Each day a subsciber will receive at least 400 transactions and 5000 commands.

Latency is 6 seconds, delivery rate 180 commands per second at less busy times.
Latency is 14 seconds, delivery rate 127 commands a second at busy times.

I have seen it get as slow as 0.04 commands a seconds at busy times.

The server becomes incredibly slow when there are more than 50 concurrent connections.

We are seeing 100 CPU for most of the day as clients connect to the distributor at various times. Lunchtime is particularly busy when people go to lunch, leaving their machines idle. We see lots of "time-outs" and "unable to connect to distributor" messages on the replication monitor during peak times.

What can we do to improve the performance of the distribution server?

Are we being over-ambitious by selection SQL Replication for this scenario?

Thank you for any help!

Best wishes

Julian

View 1 Replies View Related

Transactions Are Still Pending For Distribution After Disable Transactional Replication

Apr 11, 2007

Recently we disabled the transactional replication on a prodution database(data size:300GB) , two weeks later we were aware of the transaction log of that db almost reached the limitation (70GB).I investigated the issues and found:

1. open transactions pending for distribution:

DBCC opentran('xxxx')

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (323830:7083:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2. exec sp_replcounters, got over 240,000 replicated_transactions, which caused the tran log extremely large.

The transactional replication has been disabled on this server, there are no publication, distribution DBs any longer, all replication-related agents are also gone, why did this still happen???

3. use sp_repldone manaully reset those 240,000 transactions to distributed, check opentran, it was fine for a while. The tran log file was freed up 99% after that.

4. check open transactions and run sp_replcounters today, found the over 60000 transactions again on the db.

Did anybody experience the same issue and knows how to fix this issue and stop this happening ?

Many Thanks,

Henry

View 4 Replies View Related

Distribution Agent

Nov 13, 2002

HI,

We are having a problem with a distribution for a publication.

It is replicating email field and one of the values had a ' in it which is failing at destination.

Is there a way to get rid of this bad record from distribution agent cue?

thanks

View 2 Replies View Related

SQL SERVER 2005 REPLICATION (MERGE AGENT FAILING : PUSH SUBSCRIPTION)

Mar 27, 2006

ERROR MESSAGE:

"Message: Incorrect syntax near 'NoteID'."

Here is a snipet from the output file:

2006-03-27 13:01:49.820 Skipping file 'NotesHistory_21.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2006-03-27 13:01:49.867 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON

2006-03-27 13:01:49.898 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:49.945 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON

2006-03-27 13:01:50.008 [17%] OLE DB Subscriber 'LUZERNV4-PDOSQL4': CREATE TABLE [dbo].[MSmerge_conflict_EFILE_MERGE_NotesHistory](
[NoteID] [int] NOT NULL,
[CaseRefNum] [int] NOT NULL,
[KeyField] [varchar](500) NOT NULL,
[KeyValue] [varchar](500) NOT NULL,
[Note] [varchar](800) NULL,
[NoteField] [varchar](100) NULL,
[ScanDocID] [int] NULL,
[PDF_ID] [int] NULL,
[RescanCode] [int] NULL,
[RescanNote] [varchar](100) NULL,
[AuditID] [bigint] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
)

ꜽś�? �?śB 27 13:01ś蛪� ��?ś�?ĀPercent Complete: 17
2006-03-27 13:01:50.086 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON

2006-03-27 13:01:50.117 Preparing table 'NotesHistory' for merge replication
2006-03-27 13:01:50.164 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON

2006-03-27 13:01:50.195 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.258 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
alter table [MSmerge_conflict_EFILE_MERGE_NotesHistory] add origin_datasource_id uniqueidentifier NULL

2006-03-27 13:01:50.351 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
create nonclustered index [ncMSmerge_conflict_EFILE_MERGE_NotesHistory] on [MSmerge_conflict_EFILE_MERGE_NotesHistory] ([rowguid], origin_datasource_id)

2006-03-27 13:01:50.383 OLE DB Subscriber 'LUZERNV4-PDOSQL4': exec sp_MSsetconflicttable @article = N'NotesHistory', @conflict_table = N'MSmerge_conflict_EFILE_MERGE_NotesHistory'

2006-03-27 13:01:50.430 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.476 [17%] The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.Percent Complete: 17
2006-03-27 13:01:50.50�?Ā
2006-03-27 13:01:50.539 Applied script 'NotesHistory_21.cft'
2006-03-27 13:01:50.586 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.617 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.711 Percent Complete: 17
2006-03-27 13:01:50.742 {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.773 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.867 Percent Complete: 0
2006-03-27 13:01:50.898 Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.961 Percent Complete: 0
2006-03-27 13:01:50.992 Category:SQLSERVER
Source: LUZERNV4-PDOSQL4
Number: 102
Message: Incorrect syntax near 'NoteID'.
2006-03-27 13:01:51.039 Percent Complete: 0
2006-03-27 13:01:51.070 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-03-27 13:01:51.133 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.180 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.226 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.258 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.305 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.336 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.383 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.414 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.445 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.492 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.523 The merge process will restart after waiting 60 second(s)...

View 1 Replies View Related

Distribution Agent Basics?

Nov 6, 2006

Hey everyone,
sorry for the lame questions, but:
1) I have a transactional replication service set up. However, whenever we select a new table or column within a table to replication to the subscribers, the changes never seem to get picked up unless we make an actual change within some of the other data. Any idea why this is? Even after I've reinitialized snapshots, the new changes don't want to proprogate to teh subscribers until data is actually changed.
2) where do I need to go to manually stop and restart a a distribution agent to force these changes to be proprogated without having to change any data within the tables?
Thanks

View 1 Replies View Related

Replication - Distribution Agent

May 31, 2001

I seem to be having trouble with the distribution agent not doing what I want it to do. I have set up a few publications to run all the time but when I want to schedule a publication to run once a day this doesn't happen it defaults to run all the time.
Can the distrubition agent run some publications continusly and some publications scheduled?

Can anyone shed some light on this problem.

View 2 Replies View Related

Replication And Distribution Agent

May 3, 2000

Please, i need some help with the replication i had configured.
I have two publications to the same subscriber, one with data filters and other not.
The one without data filters i have test manualy. But now after i have created the other one, the log reader says that '145 transactions with 347 commands' were delivered, but the distribution agents says that 'no replicated transactions are avaiable'.
I know that have many things to be replicated, but doesn�t work.
The Sql server agent is working on the distributor.
There is something that can be causing this error ?????

Please, if someone can help me i apologize

Thanks in regards
Sandra Lino

View 4 Replies View Related

Distribution Agent At The Publisher

Apr 27, 2008

hi,

the distribution agent says that the process is running and waiting for response from the server .

i have replication set up for different locations.

only one location bothers me.

it runs for hours and it will not get complete for quite a few days.

once it is done i am missing the data for few days.

can you please tell to me how to make the distribution agent run faster with out much delays.

thank you,

View 1 Replies View Related

Stopping The Distribution Agent

Oct 2, 2007

Maybe I'm just totally not getting it, but can someone tell me how to stop/start the distribution agent? The Books Online document - "How to: Start and Stop a Replication Agent (SQL Server Management Studio)" - shows how to stop/start the Snapshot Agent, Log Reader Agent and Queue Reader Agent, but not the Distribution Agent. Is it that stopping one of these agents in turn stops the Distribution Agent. I am trying to work through how to add an article to a peer-to-peer topology and need to stop the agent as part of that.

Thanks,
Larry

View 3 Replies View Related

Problem With Distribution Agent

Oct 28, 2006

Can anyone help me on this??

I have a transactional replication going on from Server A to server B (Sql Server 2005)

The replication was working well till yesterday..

But now, the replicated transactions are not getting applied at the subscriber. When I see the Log reader agent, its working fine...delivering the transactions to the distributor. When I see the replication monitor status ..it says 'delivering replicated transactions'...however the number of undistriuted transactions is very high..its getting bigger and bigger..not decreasing...I dont see any errors..So my guess is the distribution agent is not working eventhough its says 'delivering replicated transactions'..Other thing is if i see the replication monitor it says status excellent...and latency as 00 ..but i dont see the latest data at the subscriber..the subscriber is way behind the publisher..

can anyone help me on this?? how do i resolve this problem??? I stopped the distribution agent and again started it..but it dint help...what should i do??

thanks

View 9 Replies View Related

Replication Distribution Agent Error

Feb 1, 2001

I have setup a replication and after 24 hours this are the error messages I have got:Last action; The database 'supersite' on server 'solomon' does not exist. Full error message: Could not locate entry in sysdatabases for database 'superiste'. No entry found with that name.Make sure that the name is entered correctly. Can anyone out there help me out?

View 1 Replies View Related

Replication - Orphaned Distribution Agent

Dec 9, 2002

I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.

How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.

Any help would be appreciated.

Colleen

View 1 Replies View Related

Intermitent Distribution Agent Errors

Mar 24, 2008

I have a replication Push Subscription configured that works most of the time but I get occasional errors satating that "The process could not connect to the Subscripber 'xxxx'". The details are

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
(Source: PYRRXNDAL341WIN (Data source); Error number: 18452)

This happens randomly and when it does fail, I can either what for the next occurence in the schedule or I can start the agent manually and it completes successfully. I can also ping the other box when it happens. I've looked at the event logs and don't see anything going on around the times the distribution agent is having the problems.

Any suggestions on where to start looking for more detail?

View 4 Replies View Related

Replication - Distribution Agent Becomes Idle

Aug 21, 2006

I have been seeing this issue for several weeks now. The distribution agent will become idle even though there is activity occurring within the replicated database. If I manually start the distribution agent, it will begin retrieving the transactions. I thought that this might be due to inactivity but I have modified the inactivity threshold and I am not receiving an actual inactive agent warning. There are no warnings -- it simply sits there and does not resume sending transactions.

Any thoughts? This is a transactional replication with a distribution agent that is run at the subscriber.

Thanks,

MV

View 1 Replies View Related

Replication Distribution Agent Does Not Start

Jul 20, 2005

Hi,For some magical reason, my database replication has suddenly stoppedworking. I modified the publication but the Distribution agent willnot run; the snapshot does not get applied to the subscriber, andchanges made at the publisher do not get replicated.My configuration is: Transactional Replication with a 'Pull'Subscription. The Subscriber is Updateable with Immediate UpdatesOnly. All created via the SQL Server Enterprise Manager. Both thePublisher and Subscriber are on the same server. I'm running SQLServer 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).I manually run the snapshop agent. It runs fine with the status'Succeeded' and the Last Action is 'A Snapshot of X article(s) wasgenerated'.When I look at the distribution agent, things start to get a bitconfusing in ( <SERVERNAME>/Replication Monitor/ Publishers/<SERVERNAME?>/ Publication/ Subscription), the status staysconstantly at 'Never Started'. All other columns are either blank or0. When I right click over the subscription, both Start Synchronisingand Stop Synchronising are NOT greyed out. If I click on either StartSynchronising or Stop Synchronising (absolutely) nothing happens(except the mouse turns 'busy' for a fraction of a second). There areno error messages.I added -output c:sqlout.txt -outputverboselevel 3 to thedistribution agent command. C:sqlout.txt did not get created.It worked fine up to a couple of days ago. I'm not sure exactly whatcaused the problem, as I made a few changes at the same time. Thechanges included: adding a new transactional publication, adding morearticles to an existing transaction publication, and adding a mergepublication. I've tried to go back to the set up before I made thechanges, even disabling replication and starting again from scratch.Still the problem persists.The owner of the distribution agent has dbo permissions in both thepublishing and subscribing database. Under 'Publisher and DistributorProperties' I've set the 'Replication Agent Connection to Publisher'and 'Agent Connection to the Subscriber' both to Use SQL ServerAuthentication, both with the same account that has dbo permissions inboth publishing and subscribing database.Has anybody come across this problem before? Can anybody make anysuggestions? Thanks in advance for your help.RegardsDamian HelmeIS ManagerINTEC Engineering (UK) Ltd.

View 1 Replies View Related

Distribution Agent Insufficient Permissions

Jan 11, 2007

Hi,

I am trying implement replication and having problem when creating push subscription to an existing transactional replication publication.
The distribution agent is failing to run its job with the error:

Agent message code 14260. You do not have sufficient permission to run this command. Contact your system administrator.

I followed the http://msdn2.microsoft.com/en-us/library/ms151868.aspx article instructions when I set the distribution agent properties

What did I miss?

The following is the step error message:
Date        1/12/2007 2:30:01 PM
Log        Job History (105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15)

Step ID        2
Server        105342-DB3PROD
Job Name        105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15
Step Name        Run agent.
Duration        00:00:00
Sql Severity        0
Sql Message ID        0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted        0

Message
2007-01-12 19:30:01.258 Microsoft SQL Server Replication Agent: distrib
2007-01-12 19:30:01.258
2007-01-12 19:30:01.258 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-01-12 19:30:01.258 User-specified agent parameter values:
            -Subscriber 105337-DEV2
            -SubscriberDB MOMA
            -Publisher 105342-DB3PROD
            -Distributor 105342-DB3PROD
            -DistributorSecurityMode 1
            -Publication ArchivedTransactions
            -PublisherDB MOMA
            -XJOBID 0x65C41EBC553D96439BAF69E4DC3CC823
            -XJOBNAME 105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15
            -XSTEPID 2
            -XSUBSYSTEM Distribution
            -XSERVER 105342-DB3PROD
            -XCMDLINE 0
            -XCancelEventHandle 00000000000006E8
2007-01-12 19:30:01.258 Startup Delay: 214 (msecs)
2007-01-12 19:30:01.477 Connecting to Distributor '105342-DB3PROD'
2007-01-12 19:30:01.618 Agent message code 14260. You do not have sufficient permission to run this command. Contact your system administrator.

View 5 Replies View Related

Distribution Agent Failed But Didn't ???

Nov 15, 2005

Hi There

View 1 Replies View Related

Distribution Agent Often Dies With The Following Message. Why?

Dec 22, 2006

Replication Distribution Agent often dies with the following log entry. At that hour little is ever going on so I am surprised that the error occurs.

Failed Job -> JobName: Instance1-DB1-Instance2-33, StepName: Run agent., Message: Timeout expired. NOTE: The step was retried the requested number of times (10) without succeeding. The step failed..

Any ideas where to start?

Thanks

View 5 Replies View Related

Distribution Agent Distrib.exe Taking Up Memory

Nov 25, 2002

Hi,
We have 32 individual publications setup on SQL 2000 and have 32 distribution agent running against it. I think each distribution agent opens a process called distrib.exe in Windows task Mgr.

Problem I'm having is that these 32 distribution agents are taking up fair amount of memory 1/2 gig and each process seem to be ever increasing in memory usage so I suspect a memory leak?

Is there something I can do to fix this problem? I don't want to upgrade memory as we already have over 1 1/2 gigs on this machine and it would not be feasible if we have a memory leak.

thanks!

View 1 Replies View Related

Transaction Replication: Distribution Agent Owner

Aug 16, 2005

Hello!

View 5 Replies View Related

Distribution Agent - Replication Monitor Warning

Nov 14, 2005

Hi,

View 1 Replies View Related

Error - Distribution Agent Failed To Create Temporary Files

Sep 17, 2014

I've been using replication for a long while now but have never come across this error. It's a basic transactional replication from ServerA to ServerB, where ServerA is also the distributor. Everything had been running fine on it until yesterday, when this error started popping up and no further transactions could be delivered.

After some quick googling I was able to determine that the distribution agent account needed write access to C:Program FilesMicrosoft SQL Server100Com. According to the MSFT article it's because the distribution agent is running under a non-default profile. I didn't change this. However, what I did change around the time that these errors started occurring was the server's Replication Max Test Size setting. It would be far too coincidental for this to not be the cause, but what I don't understand is *why* that would have changed it.

how do I change this? It is definitely not preferable to create temp files in this directory in our environment.

View 1 Replies View Related

Distribution Agent Error - (Multiple-step OLE DB Operation Generated Errors....)

Oct 18, 2005

Hi,

View 5 Replies View Related

Log Reader Agent -transactional Replication

Aug 2, 2001

Unable to start log reader agent after being manually stopped.

Any ideas.

View 1 Replies View Related

Transactional Replication && Snapshot Agent

Jun 27, 2007

Why would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?

Does this help to replicate DDL against the Subscriber?

Thanks,

Thomas LeBlanc

View 1 Replies View Related

Transactional Replication- Snapshot Agent

Sep 7, 2006

When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.

Help ME!!!!!!!!!!!!!!!

View 3 Replies View Related

SQL Server 2000 Standard Edition - Distribution Agent Dieing When Network Connection Fails

May 21, 2007

Hi,

We have replication setup on a sql server 2000. We encountered issue that the distribution agent goes down (distrib.exe stop running) in the event of network connection broken. We would like to know:

is this the expected result that the distribution agent will go down in the event of communication failure between the distribution server and subscriber server?
if not, is there a way to programmatically control and restart the agent?
Is there any sp in SQL server which can monitor the replication communication error message?
is there any sp in SQL server which can be run to restart the agent?
For the best practice, what do you think we can do to achieve an ��event-driven�� kind of mechanism so when an communication breaks, the agent can be restarted by the triggered event (or at least a simple way to automatically restart)?

Thanks

Bill

View 3 Replies View Related

SQL Server 2008 :: Transactional Replication (Snapshot Agent)

Mar 19, 2015

Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?

The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.

We have a post snapshot script that updates these additional columns on the subscriber.

I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.

Is this possible?

View 1 Replies View Related

Transactional Replication And Deadlock + Suspect Agent Errors For Over 80 Databases

Oct 25, 2005

Hi,
I have more than 80 databases on my publisher (SQL Server 2000 SP4), I tried to enable Transaction Replication on all of those databases instantly through some T-SQL programming and DTS Packages. Every thing works fine until the snapshot agents starts to take sanpshot from the publisher databases. As soon as their snapshot agents start for those 80 databases, they start giving the deadlock error. All 80 snapshot agents starts at the same time.

Error Message:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Error Detail:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: Server_Distribution (Data source); Error number: 1205)
---------------------------------------------------------------------------------------------------------------

WHY???

View 2 Replies View Related

SQL Agent Job Very Slow

Jul 21, 2000

I am configuring a new Server running SQL Server 7.0 sp2.
I have a job that runs a large process. From the SQL Agent job, the processing time is over 8 hours. If I run the same process from a query window, it takes an hour and a half. On my 6.5 database, the job takes 2 - 3 hours from the scheduled task. What is going on with SQL Agent? I have not been able to find any information on memory and SQL Agent.
Any help would be greatly appreciated!
Thanks
Trina Blazek

View 2 Replies View Related