January 20, 2009 at 2:37 am
In a SQL Server 2005 transactional replication with the distribution database on the same system as the publisher I keep getting the above mentioned errors for which I cannot find any helpful information (needless to say, the links below are blank...)
[font="Courier New"]The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
[/font]
I have deleted the publication and the subscriber and re-created them "from scratch" - the errors persists.
Can anybody please provide me with additional information and suggestions what to try to get the replication running again? Thanks a lot in advance!
January 20, 2009 at 2:58 am
Increase the value of QueryTimeout property and decrease the value of ReadBatchSize property of the Log Reader Agent profile so that the processing of the transaction log is successful.
January 20, 2009 at 3:43 am
Thanks for your prompt answer! I am currently trying (from default values QueryTimeout=1800; ReadBatchSize=500) QueryTimeout=65534 (max. allowed); ReadBatchSize=50... no avail... in the log file viewer I see the following entry: is my ReadBatchSize being overwritten from somewhere with 500000?!? If so, where could that be? Thanks!
[font="Courier New"]Date20.01.2009 11:36:49
LogJob History (SRVDB01\"JOBNAME")
Step ID2
ServerSRVDB01\SQL2005
Job NameSRVDB01\"JOBNAME"
Step NameRun agent.
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
-XSTEPID 2
-XSUBSYSTEM LogReader
-XSERVER SRVDB01\SQL2005
-XCMDLINE 0
-XCancelEventHandle 00000A20
-XParentProcessHandle 00000704
2009-01-20 10:36:49.627 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-messageinterval 14400000
-logintimeout 15
-querytimeout 65534
-readbatchsize 50
-readbatchsize 500000
2009-01-20 10:36:49.643 Status: 4096, code: 20024, text: 'Initializing'.
2009-01-20 10:36:49.643 The agent is running. Use Replication Monitor to view the details of this agent session.
2009-01-20 10:36:50.814 Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.'.
2009-01-20 10:36:50.814 The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.
2009-01-20 10:36:50.814 Status: 0, code: 0, text: 'Unspecified error'.
2009-01-20 10:36:50.814 Status: 0, code: 22037, text: 'The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.'.[/font]
January 20, 2009 at 4:18 am
in distribution server check logreader agent job step->run agent properties. you can add
-ReadBatchSize=50
January 20, 2009 at 6:35 am
Im quite confused meanwhile: please consider following statement retrieving all parameters from agent profiles which contain the "ReadBatchSize" parameter:
[font="Courier New"]SELECT pr.profile_name, pr.agent_type, pr.type, pa.*
FROM MSagent_profiles pr
JOIN MSagent_parameters pa ON pa.profile_id = pr.profile_id
WHERE pr.profile_id IN (SELECT profile_id
FROM MSagent_parameters pa
WHERE parameter_name LIKE '%ReadBatchSize%')[/font]
It retrieves following results which correspond to the values I entered in the GUI (the profile with id 17 is applied, the others given for completeness):
[font="Courier New"]profile_name agent_type type profile_id parameter_name value
----------------------------------- ----------- ----------- ----------- ------------------------- -------
Default agent profile 2 0 2 -HistoryVerboseLevel 1
Default agent profile 2 0 2 -LoginTimeout 15
Default agent profile 2 0 2 -LogScanThreshold 500000
Default agent profile 2 0 2 -PollingInterval 5
Default agent profile 2 0 2 -QueryTimeout 1800
Default agent profile 2 0 2 -ReadBatchSize 500
"PROFILENAME" 2 1 17 -HistoryVerboseLevel 1
"PROFILENAME" 2 1 17 -LoginTimeout 15
"PROFILENAME" 2 1 17 -LogScanThreshold 500000
"PROFILENAME" 2 1 17 -MessageInterval 3600
"PROFILENAME" 2 1 17 -PollingInterval 5
"PROFILENAME" 2 1 17 -QueryTimeout 65534
"PROFILENAME" 2 1 17 -ReadBatchSize 50
Verbose history agent profile. 2 0 3 -HistoryVerboseLevel 2
Verbose history agent profile. 2 0 3 -LoginTimeout 15
Verbose history agent profile. 2 0 3 -LogScanThreshold 500000
Verbose history agent profile. 2 0 3 -PollingInterval 5
Verbose history agent profile. 2 0 3 -QueryTimeout 1800
Verbose history agent profile. 2 0 3 -ReadBatchSize 500
19 row(s) affected)
[/font]
When running the Log Reader Agent I receive following message in the Log File Viewer:
[font="Courier New"]
Date20.01.2009 14:26:20
LogJob History (SRVDB01\SQL2005-JOBNAME-6)
Step ID2
ServerSRVDB01\SQL2005
Job NameSRVDB01\SQL2005-JOBNAME-6
Step NameRun agent.
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
-XJOBNAME SRVDB01\SQL2005-JOBNAME-6
-XSTEPID 2
-XSUBSYSTEM LogReader
-XSERVER SRVDB01\SQL2005
-XCMDLINE 0
-XCancelEventHandle 00000A24
-XParentProcessHandle 0000088C
2009-01-20 13:26:21.116 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-messageinterval 3600000
-logintimeout 15
-querytimeout 65534
-readbatchsize 500000
2009-01-20 13:26:21.132 Status: 4096, code: 20024, text: 'Initializing'.
2009-01-20 13:26:21.132 The agent is running. Use Replication Monitor to view the details of this agent session.
2009-01-20 13:26:22.382 Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.'.
2009-01-20 13:26:22.382 The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.
2009-01-20 13:26:22.382 Status: 0, code: 0, text: 'Unspecified error'.
2009-01-20 13:26:22.382 Status: 0, code: 22037, text: 'The process could not execute 'sp_replcmds' on 'SRVDB01\SQL2005'.'.[/font]
The ReadBatchSize parameter is displayed with the value of the LogScanThreshold for profile "17"... which would of course explain my original problem... does anyone know which parameters are actually applied by the Log Reader Agent?!?
Thanks!
January 20, 2009 at 8:34 am
Additionally, I have specified following definition of step 2 "Run agent." in the Log Reader Agent:
[font="Courier New"]-Publisher [SRVDB01\SQL2005] -PublisherDB ["DATABASE"] -Distributor [SRVDB01\SQL2005] -DistributorSecurityMode 1 -ReadBatchSize 50 -Continuous[/font]
... which doesn't seem to impress the execution (or at least the log output, cp. above) a lot - am I missing something?
January 20, 2009 at 7:38 pm
try to recreate the replication and include the distribution server.
January 22, 2009 at 9:57 am
Tried that already... no such luck... tonight I'll try rebooting the publisher server - poor thing's been up for a couple of months 😉
April 19, 2012 at 12:15 am
Same I am getting
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply