Dbremote generates a lot of 1kb files, transactionlog only contains commits

[mmangels]

Lately our consolidated database generates a lot of 1kb files (~300 files per user). When I translate the transaction log file, I only see commits. All statements like this:

--BEGIN TRANSACTION-1011-06217481851
BEGIN TRANSACTION
go
--COMMIT-1011-06217481885-2020-04-24 02:37:35.209
COMMIT WORK
go

Is this the cause of those 1kb files? That just the COMMIT WORK statement is replicated?

you can find the transactionlog + the translation here: https://we.tl/t-4lyIRnExS6

[VolkerBarth]

Do you run DBREMOTE on the cons in continuous or in batch mode? With the latter, the "ping messages" happen very frequenently, whereas continuous mode has some logic to prevent those...

[mmangels]

I run it in batch mode

[regdomaratzki]

If SQL Remote has sent a message to the remote database but has not yet received a confirmation from the remote database that the message has been applied (in other words log_sent > confirm_sent), even if there is no data to be sent to the remote database, SQL Remote will send an empty "Ping" message to the remote database that essentially asks it to send a confirmation that it has applied the last messages. This is done in case a message sent to the remote database is sent and then lost, but some time goes by before more data needs to be sent to the remote database. The empty "ping" message will ensure that the remote database realizes that a message is missing.

Reg

[regdomaratzki]

If you pick one of the remote database with a large number of small files in the message system (let's call them "rem1") and then look in the SQL Remote logs from the consolidated database, do you see entries in the SQL Remotes log along the lines of "Received message from rem1 (0-XXXXXX-YYYYYY-0)" and "Applying message from rem1 (0-XXXXXX-YYYYYY-0)" ?

[mmangels]

yes. So those are just 'ping' messages? Receiving -> applying -> unlink. Other messages actualy contain the statements: Receiving -> applying -> {statements} -> unlink

[regdomaratzki]

Correct. if you are running dbremote with -v and there are no SQL statements in the message being applied, this is a ping message.

If there are ~300 of these messages for each remote user, it sounds like dbremote runs in batch mode ~300 in between the time that remote databases apply incoming messages from the consolidated database.

Reg