|
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?
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? |
|
So in the end, based on Jason's and John's (somewhat contradictory) answers, it seems that
(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:
|
|
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 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.
(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
|
|
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. 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
|
|
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.log Now I'm somewhat puzzled:
(17 Mar '11, 04:18)
Volker Barth
|