Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I need some clarification on working with transaction logs that have been truncated using the -r switch within the dbbackup utility.

So I'm working with a company that is handling large amounts of medical data across roughly 10 SQL Anywhere databases. All of these databases are running in close to the latest release of SQL Anywhere 17.0.11. Something around there. Running in Linux.

In the past they have been fully backing up their databases, but their transaction logs would get so large, they would need to shut their databases down, delete the transaction logs, then restart the database and start it with the option to create a new transaction log.

So now were adding log based replication to the mix.

The process will go as follows.

At the top of the hour, they will run replication against the production level database, sending the replication files to the appropriate directories for the remote databases. This will be ran in send only mode.

At 15 minutes past the hour, replication will be run in a receive only mode on the two remotes.

At the bottom of the hour, they will run the dbbackup utility against their databases using the -r switch. Which we know stops the current transaction log. Copies it to a new name, and restarts a new transaction log for the databases.

At 45 minutes past they hour, they run a validate against all the databases, including the backed up databases.

Which leads to my question now....

Even though the transaction logs are being copied to new transaction log files, over time, these transaction logs will continue to swallow up the server.

Is it safe to delete these partial transaction logs at any particular time with replication running in the mix?

Since replication will have already ran on both sides every hour, any changes would have already been replicated.

My understanding is that partial replication logs would need to be kept in the instance when you have a stretch of time that goes by and one of your remotes has not replicated. That will not be the case here.

If anyone could add some clarification to this, I would greatly appreciate it.

asked 04 Oct '23, 15:41

Jeff%20Gibson's gravatar image

Jeff Gibson
1.9k436474
accept rate: 20%

1

Is it safe to delete these partial transaction logs

Safe in what way? Safe to delete because dbremote will no longer need the offline transaction log or safe to delete because the transaction logs will no longer be needed for recovery?

Reg

(04 Oct '23, 15:50) Reg Domaratzki
Replies hidden

In this case both Reg.

(04 Oct '23, 16:23) Jeff Gibson

When you run dbbackup -r, dbbackup makes a backup of the transaction log into the directory you specify AND renames and restarts the active log. You end up with two copies of the same transaction log, one in the directory where the active transaction log resides, and one in the backup directory you specified on the dbbackup command line.

The renamed transaction logs in the same directory where the active transaction log reside are typically used by dbremote, and you can set the DELETE_OLD_LOGS database option so that dbremote will automatically delete renamed logs in this directory when it determines they are no longer needed.

Transaction logs placed in the backup directory specified on the dbbackup command should NEVER be scanned by dbremote. Transaction logs in this directory are there for recovery. It's up to you to decide when it's safe to remove them, which is typically after a full backup.

Reg

permanent link

answered 04 Oct '23, 16:42

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

Appreciate the follow up with this Reg. This would actually be the way to go.

(04 Oct '23, 16:56) Jeff Gibson
Replies hidden
1

Yes, DELETE_OLD_LOGS is one of those nice "Zero administration overhead" SQL Anywhere goodies, both on the consolidated and remote sites... - We have usually set it to "DELAY".

(05 Oct '23, 04:38) Volker Barth
Comment Text Removed

That's good info to have on that option Volker. Thanks for the extra details on that.

(05 Oct '23, 10:04) Jeff Gibson
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:

×60
×59
×30
×18

question asked: 04 Oct '23, 15:41

question was seen: 359 times

last updated: 05 Oct '23, 10:04