Skip to content

In AWS DMS for SQL Server is there a way to specify the setting logScanBatchSize

0

When DMS reads from a SQL Server TRN file ( maybe starting in 3.5.3 ) it uses the function called fn_dump_dblog() to read .TRN backups from the database server.

By default the software utilized by DMS does not limit the records pulled or might have a default of 50000

logScanBatchSize might be available to say select top 1000 and allow the system to run. I don't see performance issues on SQL, but it does appear to cause latency on DMS when the TRN file is large ( 10 gigs )

select /* top 50000 */
	[Current LSN],
	[operation],
	[Context],
	[Transaction ID],
	[Transaction Name],
	[Begin Time],
	[End Time],
	[Flag Bits],
	[PartitionID],
	[Page ID],
	[Slot ID],
	[RowLog Contents 0],
	[Log Record],
	[RowLog Contents 1] -- After Image
from sys.fn_dump_dblog (NULL, NULL,NULL, 1,'C:\Backup\backup.TRN'...

Existing code looks like above. The 50000 is commented out probably indicating its being set behind the scenes to 0 or -1, not sure.

Is there a way we can control this to see if top 1000 etc makes a difference? I know sometimes the TRN may have large quantities of unrelated transactional records that might be involved.

When using dbcc outpbuffer(spid) I notice that it appears to only be grabbing a few records every minute or so leading to high CDC source latency and consequentially high target latency. Even when DB activity was relatively light(few thousand records an hour ).

Any help would be appreciated.

Thank you

asked 2 years ago554 views

1 Answer
0

I couldn't find an answer to this, but it wasn't the reason fn_dblog was running slowly. We had too many tempdb files on different drives. Some old config.

""" So after upgrading from 3.4.x to 3.5.x our latency from SQL Server got super slow. This was a forced upgrade by AWS.

Turns out our production instance had a 8 tempdb files. Some where on the Z: drive and some on the C: drive. Not sure why this freaked SQL Server out, but fn_dblog performance got very poor. Tempdb verison store old transaction time steadily increased.

Dropped down to 2 tempdb files both on Z and contention went away. Luckily we had a QA system where we didn't have that config and saw the Tempdb stats in one of our monitoring tools.

I did notice the syntax of the fn_dblog call changed from 3.4 to 3.5 with a few additional parts of the where clause. Like a lot of things SQL server probably uses the tempdb during the fn_dblog calls...wish they would just admit it needs to be a documented function. """

answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.