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

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?

dbtools sql-remote dbtran

asked 16 Mar '11, 18:01

Volker Barth
40.2k●361●550●822
accept rate: 34%

flat view

4 Answers:

active answers oldest answers newest answers popular answers

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

there is no real parsing-free solution to get the ending offset and
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.

permanent link

answered 19 Mar '11, 08:02

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 22 Mar '11, 04:51

flat view

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.

permanent link

answered 17 Mar '11, 09:47

Jason Hinspe...
2.7k●6●34●47
accept rate: 35%

19 Mar '11, 07:55

Volker Barth
40.2k●361●550●822

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?

(17 Mar '11, 10:13) Volker Barth

Replies hidden

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.

(17 Mar '11, 14:04) Jason Hinspe...

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

(17 Mar '11, 17:23) Justin Willey

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:)

(18 Mar '11, 04:57) Volker Barth

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

(18 Mar '11, 04:58) Volker Barth

flat view

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.

permanent link

answered 18 Mar '11, 09:55

John Smirnios
12.0k●3●96●166
accept rate: 37%

19 Mar '11, 07:45

Volker Barth
40.2k●361●550●822

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.

(18 Mar '11, 10:09) Volker Barth

flat view

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%)

permanent link

answered 16 Mar '11, 20:08

Jason Hinspe...
2.7k●6●34●47
accept rate: 35%

edited 16 Mar '11, 20:14

Now I'm somewhat puzzled:

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.)
So you say there's no way to get these offset numbers without parsing?

(17 Mar '11, 04:18) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sql-remote ×103
dbtools ×15
dbtran ×13

question asked: 16 Mar '11, 18:01

question was seen: 6,592 times

last updated: 22 Mar '11, 04:51

SAP SQL Anywhere

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

Follow this question

Related questions