ReplTip – Setting LogScanThreshold can result in LogReader failure
Content provided by Sateesh Yele, Microsoft SQL Escalation Services
BOL/MSDN documents –Logscanthreshold as “internal use only”.
https://technet.microsoft.com/en-us/library/ms146878(v=sql.110).aspx
-LogScanThreshold scan_threshold
Internal use only.
Other blog postings imply how to adjust this setting for better Logreader agent performance. If you add this parameter to the Logreader SQL Agent command line and start the agent, Logreader agent will fail and report “syntax error”.
2015-09-21 15:24:30.433 Status: 0, code: 20030, text: 'A syntax error occurred near parameter '-LogScanThreshold'.'. 2015-09-21 15:24:30.449 A syntax error occurred near parameter '-LogScanThreshold'
You’ll notice the parameter does appear under the Replication Monitor Agent Profiles. This allows you to create a NEW agent profile and change the LogScanThreshold parameter.
In older versions of SQL Server (before SQL 2012) we’ve seen a couple of incidents where changing this value has resulted LogReader agent failing with “cannot insert duplicate key into msrepl_transactions table” error.
Default value should work for most environments. If you believe changing this value reduces latency, test your Log Reader under various conditions before changing this parameter. You may discover other factors such as transaction batch size had larger impact than LogScanThreshold.
–Chris Skorlinski
