January 21, 2009 at 5:56 am
(This is a partial re-post of thread "Log Reader Agent Errors MSSQL_REPL20011 / MSSQL_REPL22037")
I seem to be observing a parameter mix-up in a transactional replication. The parameter "ReadBatchSize" which I set in the Agent Profile GUI can be retrieved correctly from the system databases as follows:
[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]
The query 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"... does anyone know which parameters are actually applied by the Log Reader Agent?!? Is just the error message wrong or is there a real mix-up with the parameters? How can I make sure the value I set is applied to the (currently failing!) replication?
Thanks!
January 21, 2009 at 11:00 am
Swiss-Al (1/21/2009)
(This is a partial re-post of thread "Log Reader Agent Errors MSSQL_REPL20011 / MSSQL_REPL22037")I seem to be observing a parameter mix-up in a transactional replication. The parameter "ReadBatchSize" which I set in the Agent Profile GUI can be retrieved correctly from the system databases as follows:
[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]
The query 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"... does anyone know which parameters are actually applied by the Log Reader Agent?!? Is just the error message wrong or is there a real mix-up with the parameters? How can I make sure the value I set is applied to the (currently failing!) replication?
Thanks!
1. To ensure you are using the "new" profile you should STOP and RESTART the agent.
2. Verify That when you check the profile selected your agent is actually set up for that profile in the UI
3. Verify that in the actual SQL Agent Job there is nothing overriding your profile in the command line.
Good luck,
* Noel
January 21, 2009 at 1:56 pm
@noeld: thanks for your suggestions;
- I had already stopped/started the agent - multiple times 😉
- The appropriate profile is checked for the agent in use
- Using a command line parameter (i.e. e.g. "[font="Courier New"]-ReadBatchSize 50[/font]") shows up as "duplicate" line ("[font="Courier New"]-readbatchsize 50[/font]" above "[font="Courier New"]-readbatchsize 500000[/font]") in the error message in the log... this, together with the fact that the "500000" line changes with the LogScanThreshold parameter leads me to think that there is a bug with the output of the error message as opposed to the parameter actually used for the replication.
Can anyone confirm this?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply