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

asked 24 Apr, 03:22

MichaelMangelschots's gravatar image

MichaelMange...
1252311
accept rate: 0%

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...

(24 Apr, 10:01) Volker Barth

I run it in batch mode

(24 Apr, 10:07) MichaelMange...

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

permanent link

answered 24 Apr, 09:30

Reg%20Domaratzki's gravatar image

Reg Domaratzki
6.6k337101
accept rate: 39%

edited 24 Apr, 09:31

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)" ?

(24 Apr, 09:31) Reg Domaratzki

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

(24 Apr, 10:08) MichaelMange...
Replies hidden

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

(24 Apr, 10:23) Reg Domaratzki
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×94
×83
×65

question asked: 24 Apr, 03:22

question was seen: 80 times

last updated: 24 Apr, 10:23