How to get the ending log offset for a SQL Remote database to be migrated?

[VolkerBarth]

I'm about to automate a SQL Remote setup migration process. Say, the manual process is well-understood.

One necessary step according to the docs is to note the ending offset of the database's current transaction log file.

That must be done on a shutdown database.

What's the best way to do this automatically?

• When using DBTRAN, the starting and ending offsets are given within the text output of that utility. Therefore, some kind of pattern matching is necessary to get the values (and texts may vary for different languages - in my case, DE or EN) - so that's not a very precise information. For example:

Transaction log starting offset is 0001086520
SQL Remote truncation offset is 0001104772
Transaction log current relative offset is 0000000000

• I have hoped the DBTools DBTranslateLog() function would give more precise results but it just returns the same output to my application. (With some more thoughts, that's absolutely expected behaviour, as that function is the underlying gear for DBTRAN.)
• I could query the running database with db_property('CurrentRedoPos') and get a precise number but that seems dangerous as starting/stopping the database might have effects on the offset, and that's obviously not wanted. As mentioned, the offset should be calculated from a shutdown log file.

Is there an easier way to get the precise number of this offset, or do I need to do some kind of (error-prone) text parsing?

[VolkerBarth · Accepted solution]

So in the end, based on Jason's and John's (somewhat contradictory) answers, it seems that

1. there is no real parsing-free solution to get the ending offset and
2. DBTRAN should be used (as to the docs) insted of DBLOG to avoid problems with not cleanly shutdown databases.

(As I understand, one should migrate cleanly shut down databases, and then the output of DBTRAN and DBLOG should match w.r.t. to ending offset.)

---

Just to add: "derwin" has added a comment on DCX for the referenced page:

The documentation is correct as it stands in that using the dbtran utility is the most reliable way to obtain the accurate ending log offset for the database's current transaction log file.

[Former Member]

1) I don't know why the doc. recommends dbtran. It seems to me that dblog gives the same info., and should do it faster, especially for large logfiles. You have to use dblog to zap the offsets anyway, so why not use it to get them as well
2) The offset information is only dumped out as part of the message callback functions, so yes, you will have to parse those messages to get this information. It might be worth posting an enhancement request to expose a "GetLogInfo()" function in the dbtools api that returns this information specifically, or that the DBChangeLogName() function return this information in a more easily processed way.

[VolkerBarth]

Jason, thanks for the clarification. Guess I'm gonna use DBLOG then. - So you agree that it's no real option to query the running database (as the shutdown would possibly change the offset afterwards), don't you?

[Former Member]

Yes, I agree that starting the database to query it for log offsets is not a good idea. The log offset information will change - at a minimum the server does a checkpoint on shutdown, which will advance the ending log offset.
You could figure out what that adds to the log and add it to the result of your query, but there is no guarantee this value is constant, or that the server won't do other things (like more than one checkpoint), advancing the log further.

[justin_willey]

If you started the db in read only mode, would the offset change then? If that worked, Volker could avoid the parsing.

[VolkerBarth]

That's a good idea, methinks. - Nevertheless, I think it's wayyy easier to do the actual parsing than to assure the offset information taken from a running database is valid. - In the end, I'm primarily concerned with avoiding to re-extract all those remotes:)

[VolkerBarth]

Sidenote: I like nested comments - thanks to Graeme and Nathan:)

[johnsmirnios]

dblog essentially reports the expected "start" log position for a database. This is the offset at which recovery would begin if recovery were needed. For a cleanly shutdown database, it is also the offset of the end of the last log.

dbtran returns the end offset for the given log. For a database which did not shutdown cleanly, the log end offset can only be determined by parsing the log since the last log operation may only have been partially written.

[VolkerBarth]

John, thanks for the clarification - so I'll stick to DBTRAN as documented. Though I'm definetely expecting databases that are cleanly shutdown DBTRAN seems more appropriate then - it's the log offsets according to the log file that must match.

[Former Member]

The dblog utility will give you the information you need. This uses the DBChangeLogName() function. However, you may still have to do some parsing to get the actual number.

eg.
[C:temp]dblog test.db
SQL Anywhere Transaction Log Utility Version 12.0.0.2566
"test.db" is using log file "test.log"
"test.db" is using no log mirror file
Transaction log starting offset is 0000495001
Transaction log current relative offset is 0000004142

[C:temp]dblog test.log
SQL Anywhere Transaction Log Utility Version 12.0.0.2566
File "test.log" is a transaction log file
File version is 41
Log starts at offset 0000495001
Log ends at offset 0000499143
Log contains 80 pages with a page size of 4096 bytes
4142 bytes in use (1.26%), 323538 bytes free (98.74%)

[VolkerBarth]

Now I'm somewhat puzzled:

1. The according doc recommends DBTRAN to get the ending offset, not DBLOG (though the values should be the same). Which is preferable? (Of course, I'm using DBTRAN > NUL as I'm not interested in the translated log contents at all.)

2. So you say there's no way to get these offset numbers without parsing?