MobiLink Transactional Upload creates a huge temp file

[Former Member]

I have to run MobiLink synchronization with the -tu parameter due to the database design so I cannot change that.

Yesterday a yearly cleanup was performed in the database, altering some ten-thousand rows and creating new ten-thousand rows (approximate)

Now I have 13 000 000 transactions/log offsets to synchronize from the remote database which in it's own should not present any problem.

This creates a temp file with the size of 92 gigabytes (!)

The database file is 89 megabytes and the transaction log file is 497 megabytes.

How is it possible that the temp file for synchronization becomes to large ?

Add to that; there are 15 remote databases and one consolidated located on the server. The temp files does not seem to be cleaned up immediately, in a worst case scenario with scheduled synchronizations triggering one-after-another, should we estimate that we need about 1,5 terrabyte of free space to hold the temporary files ?

[Breck_Carter]

Please tell us what version of SQL Anywhere you are using for the consolidated and remote databases.

Please edit your question to explain exactly which database(s) and which temp file(s) you are referring to in each point (consolidated versus remote), and explain exactly WHEN the temp file grew to 92 gigabytes (when the cleanup ran, when dbmlsync ran, when mlsrv ran).

Having said that, a 92G temp file for an 82M database is truly grotesque, and is usually the result of a runaway query... what statements were used to perform the cleanup?

A 497M transaction log file for an 82M database is also [cough] unusual, but that's for another conversation involving dbmlsync -x.

FYI the temporary file associated with each database should be automatically deleted and recreated when the database is stopped and restarted.

[regdomaratzki]

The log scanning code is much more efficient with respect to performance and memory usage when scanning offline logs as opposed to the active transaction log since there are a lot of assumptions we can make since we know the file will not change.

If you shut down the database engine, rename the active transaction log and then try and synchronize again, does that have any effect on the temp file usage?

Reg

[VolkerBarth]

@Reg: The better performance of scanning offline log files is important information IMHO - does this hold for all log-scanning tools generally?

I.e. when using ML clients or SQL Remote, would there be a general recommendation to rename the logs (particularly on SR consolidated sites) "more often", or would this only apply when logs have to be re-scanned, say because of lost messages and the like?

(Note, we do not use DBREMOTE -u, i.e. the newest messages must be scanned from the current online log...)

[regdomaratzki]

I'm about 95% sure that the advice is only really needed for dbmlsync because of the algorithm for handling collescing (sp?) of operations.

Also, much depends on the size of the active transaction log. I don't think you'll notice a difference with "normal" sized active transaction logs in the 1-10MB range. A simple stored procedure in the database handles the restarting and renaming of the active transaction log for you.

create procedure sp_hook_dbmlsync_begin () begin backup database directory '' transaction log only transaction log rename match; end;

Don't just throw this SP into a database wihtout considering your existing backup and recovery strategy of course. The above procedure works great if your recovery strategy for remote databases is simply a re-extract from the consolidated database, which IMHO can be a legitimate strategy.

Reg

[Breck_Carter]

If your recovery strategy for remote databases is simply a re-extract then a backup isn't necessary at all, just dbmlsync -x 0