PollingInterval and HistoryVerboseLevel

I have read, and it has been recommended here, that in order to improve
transactional replication performance I should set the PollingInterval and
HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent jobs.
As we are utilizing scripts to enable and configure replication, I was
wondering if there were any built-in stored procedures that might help me do
this. I was able to use the @.optional_command_line switch on
sp_addsubscription to get these parameters set for the Distribution Agent.
Is there anything similar for the Log Reader Agent? I’d really prefer not to
have the users modify the Log Reader Agent job.
Thanks,
Mark
Mark,
I'd probably investigate sp_update_agent_profile for this. As far as i know
it cant be done pre-emptively.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Don't set the historyverboselevel to 0 for the log reader. Use profiles to
package up your agent properties and then modify the job to use the
different profile. Also have a look at -DefinitionFile to bundle up addition
properties not present in the profile.
You can change the profile used by using sp_update_agent_profile. You can
change the command line arguments by using sp_update_jobstep
Here is an example:
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
SELECT @.JobID = 0x644DDB4D684E324080CB1C265740831E
EXECUTE @.ReturnCode = msdb.dbo.sp_update_jobstep @.job_id = @.JobID,
@.step_id = 2, @.command = N'-Subscriber [DEV-HCOTTER\SQL2000] -SubscriberDB
[pubs] -Publisher [DEV-HCOTTER\SQL2000] -Distributor
[DEV-HCOTTER\SQL2000] -DistributorSecurityMode 1 -PublisherDB
Northwind] -Continuous -OutputverboseLevel 3', @.database_name = N''
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:14890C83-64E9-4C97-8418-49D5E4405B46@.microsoft.com...
>I have read, and it has been recommended here, that in order to improve
> transactional replication performance I should set the PollingInterval and
> HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent
> jobs.
> As we are utilizing scripts to enable and configure replication, I was
> wondering if there were any built-in stored procedures that might help me
> do
> this. I was able to use the @.optional_command_line switch on
> sp_addsubscription to get these parameters set for the Distribution Agent.
> Is there anything similar for the Log Reader Agent? I'd really prefer not
> to
> have the users modify the Log Reader Agent job.
> Thanks,
> Mark