December 13, 2016 at 7:22 pm
I started getting the below error message on one of the distribution agent(happened 3 times over the last three days). The publication has one push and on pull subscription. The error appeared on the push subscription only. The publication server has 5 other publication each having pull and push subscription to the same servers and all of them works fine.
I tried changing the distributor agent profile and restarted the distribution job but didn't work. I had to drop and recreate the subscription to solve it. It would be good to find the root cause.
FYI - Database mail is not configure on the server, also since other push subscriptions are find I am not sure if Database mail has anything to do with it.
Below is the Distributor Log
Message
2016-12-05 00:41:45.429 Copyright (c) 2014 Microsoft Corporation
2016-12-05 00:41:45.429 Microsoft SQL Server Replication Agent: distrib
2016-12-05 00:41:45.429
2016-12-05 00:41:45.429 The timestamps prepended to the output lines are expressed in terms of UTC time.
2016-12-05 00:41:45.429 User-specified agent parameter values:
-Subscriber SubscriberServer
-SubscriberDB DBName
-Publisher Publisher1
-Distributor DistributerServer
-DistributorSecurityMode 1
-Publication DBName_HA_Pub
-PublisherDB DBName
-Continuous
-XJOBID 0x0D2A9EB2B6FBDF4CAE11F0A700401787
-XJOBNAME Publisher_instance_-DBName-DBName_HA_Pub-SubscriberServer-48
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER DistributerServer
-XCMDLINE 0
-XCancelEventHandle 0000000000006888
-XParentProcessHandle 00000000000083E8
2016-12-05 00:41:45.429 Startup Delay: 668 (msecs)
2016-12-05 00:41:46.101 Connecting to Distributor 'DistributerServer'
2016-12-05 00:41:46.179 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 2
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2016-12-05 00:41:46.179 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.226 Initializing
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.304 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:41:51.351 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:51.351 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:51.367 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:01.390 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:01.390 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:01.406 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:16.423 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:16.423 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:16.454 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:36.474 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:36.474 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:36.505 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.524 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:43:01.524 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:43:01.540 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.556 Agent message code 14607. profile name is not valid
2016-12-05 00:43:01.571 Category:COMMAND
Source: Failed Command
Number:
Message: if @@trancount > 0 rollback tran
2016-12-05 00:43:01.571 Category:NULL
Source: Microsoft SQL Server Native DBName 11.0
Number: 14607
Message: profile name is not valid
December 16, 2016 at 7:28 am
doesnt seem like it is issue with Distribution Agent profile as it got the values for the given profile:
2016-12-05 00:41:46.179 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 2
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
How is this configuration looks like? other replication subscriptions (that are working) are going to same database?
U mentioned dbmail is not configured. On subscriber as well?
Maybe there is some table triggers or database triggers that are trying to use DBmail on subscriber?
It is hard to say without knowing the configuration.
BartL
Replication Blog
December 21, 2016 at 3:11 pm
To answer some of the question. So I have five database that were already replicated to a reporting sql server using pull subscription.
We are migrating to virtual server to replace the existing reporting server and this is where the new subscriptions come in only difference being we are using push.
So its the same database same publication but a new subscription using push and the issue is just for one publication/database and only to the new push subscription.
DB mail is not configured on subscription.
We dont use triggers, but I can double check if there is anything.
December 22, 2016 at 7:46 am
Correct me if Im wrong there are more than one subscriptions going into same database? One is pull and second push?
The only big difference between these two is the location of the distribution agent....
I tried to replicate this error u have by setting up trigger on replicated table (on subscriber) that suppose to send email with database mail (db mail is not configured) and I got this:
2016-12-22 14:35:53.673 User-specified agent parameter values:
-Subscriber MAINTEST\SUBSCRIBER
-SubscriberDB Copy_adventureworks2014
-Publisher MAINTEST\PUBLISHER
-Distributor Maintest\DISTRIBUTOR
-DistributorSecurityMode 1
-Publication Test_Pub
-PublisherDB AdventureWorks2014
-Continuous
-XJOBID 0x5F7A4C1F72DAD5488824AE781AFBF336
-XJOBNAME MAINTEST\PUBLISHER-AdventureWorks2014-Test_Pub-MAINTEST\SUBSCRIBER-1
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER Maintest\DISTRIBUTOR
-XCMDLINE 0
-XCancelEventHandle 00000000000019D0
-XParentProcessHandle 00000000000019B0
2016-12-22 14:35:53.673 Startup Delay: 5381 (msecs)
2016-12-22 14:35:59.063 Connecting to Distributor 'Maintest\DISTRIBUTOR'
2016-12-22 14:35:59.235 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2016-12-22 14:35:59.235 Connecting to Subscriber 'MAINTEST\SUBSCRIBER'
2016-12-22 14:35:59.408 Initializing
2016-12-22 14:35:59.470
42000 profile name is not valid 14607
2016-12-22 14:35:59.501 Disconnecting from Subscriber 'MAINTEST\SUBSCRIBER'
2016-12-22 14:35:59.501 Connecting to Subscriber 'MAINTEST\SUBSCRIBER'
Exactly same error. I would look for any table or database triggers or anything else that is trying to use database mail.
BartL
Replication Blog
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply