Hi,

I'm using SQLA 16 and I got strange behavior of dbbackup. If run dbbackup with "-xo" option to database which transaction log is mirroring, then dbbackup generate dateXX.mlg (e.g. 140815AA.mlg). I checked manuals, but I could not find the behavior. Is it design of dbbackup ? What is dateXX.mlg ? And is it possible to delete dateXX.mlg ?

Thanks. Tsuyoshi Yagi

asked 15 Aug '14, 01:46

Tsuyoshi%20Yagi's gravatar image

Tsuyoshi Yagi
16113
accept rate: 0%


MLG files are mirror log files.

Have you looked at: http://dcx.sybase.com/index.html#sa160/en/dbadmin/dbbackup.html*d5e54555 ?

The description of -xo says: Do not use this option if you are using database mirroring. See Transaction log file management in a database mirroring system.

Are you using database mirroring?

permanent link

answered 15 Aug '14, 11:03

JBSchueler's gravatar image

JBSchueler
2.0k2836
accept rate: 16%

Thank you for your reply !
I used mirroring only log.(It is set by dblog -m option).
Does "Do not use this option if you are using database mirroring." apply this case ?

Thanks.

(15 Aug '14, 19:44) Tsuyoshi Yagi
1

You are correct that database mirroring and transaction log mirroring are different cases. But if you are going to the trouble of mirroring the transaction log, it sounds like you want to ensure that you have a backed-up transaction log in case you lose the primary transaction log. So it doesn't quite make sense that you want to use -xo to trash the primary transaction log and/or the backed-up copy. You would not be able to recover your database from a transaction log if something were to happen to it.

(18 Aug '14, 09:53) JBSchueler
Replies hidden

it doesn't quite make sense that you want to use -xo to trash the primary transaction log

I agree. The use of dbbackup -xo implies "I DON'T CARE about rolling forward transaction log entries after restoring an old backup of the database file" which is OK in certain circumstances; e.g., an embedded application where the database can be recreated via other means whenever that is necessary.

However, the use of dbinit/dblog -m implies "I CARE SO MUCH about preserving transaction log entries that I'm willing to have two copies of the transaction log file maintained by the server" which is OK in other situations; e.g., your data is so precious to you that you're willing to do almost anything to protect it.

If dbinit/dblog -m is really desired, perhaps dbbackup -r or -x would be more appropriate.

(18 Aug '14, 10:23) Breck Carter

As Breck suggests, -r is a good option. The steps outlined in the documentation for -r also apply to the mirrored transaction log.

The <dbname>.log file that is written to the backup directory that you specify with dbbackup is the fragment of transaction log going forward from the previous time you used -r.

To completely restore your database from the transaction logs only, you will use either the YYMMDDxx.log and <dname>.log files or the YYMNDDxx.mlg and <dbname>.mlg files.

Your alternative recovery method will be the backed up <dbname>.db file and its <dbname>.log fragment. However, this backup won't be up-to-date. You also won't be able to stage your recovery (for example, when you discover that something awful happened to one of your tables and you have already run your backup a few times).

This is why you don't want to erase your YYMMDDxx.log or YYMMDDxx.mlg files. Together with the current log/mlg file they comprise your backup.

(18 Aug '14, 11:13) JBSchueler

Hi Jack and Breck,

Thank you for your reply.
If transaction log does not be mirrored, then dbbackup "-xo" does not create file.
Manual said -xo is "truncate log without backup" and manual does not said about "dateXX.log".

And if transaction log is mirrored and dbbackup "-r", then dateXX.log and dateXX.mlg (e.g. 140819AA.log 140819AA.mlg) is created.
Manual said "-r" create "dateXX.log".

So I think it is incorrect behavior (dateXX.mlg file created) when dbbackup -xo with transaction log mirroring.

By the way, I don't use SQL Remote Message Agent, so I think no problem about delete dateXX.mlg.

Thanks.

(19 Aug '14, 00:40) Tsuyoshi Yagi
Replies hidden

The manual does not say ANYTHING about what dbbackup does with the mirror log created by transaction log mirroring. You can complain about that fact (lack of documentation) but you can't say there's a bug if there's no specifications :)

If you want dbbackup -xo to stop creating .mlg files, then post an enhancement request on this forum, and give a business reason why you want that (we really want to know!)

(19 Aug '14, 05:37) Breck Carter
2

One could interpret the current behavior as -xo will truncate the primary transaction log, but if you are mirroring the transaction log, then you must really want a backup so we will do the "-r" thing to the mirroring transaction log, rather than just blow it away. The documentation does not speak about this behavior but it does have the following statement.

"Using this option can result in a database that cannot be recovered from media failure."

It uses the word "can" rather than "will". The "can" case applies when you aren't mirroring the transaction log.

You haven't really said why you are using transaction log mirroring in the first place. If you want -xo to apply to both logs, why use transaction log mirroring at all?

(19 Aug '14, 11:11) JBSchueler
1

The manual does say ANYTHING about what dbbackup does with the mirror transaction log.

Am I right that you mean "does not say ANYTHING"? - That's just in order to avoid further confusion between "transaction log mirroring" (which is not referenced on that doc topic) and "database mirroring" (which is). - Besides that, I certainly agree fully on your and Jack's point of view.

(19 Aug '14, 11:39) Volker Barth

Thanks... I only reviewed my comment half a dozen times, not the usual dozen :)... it has been fixed.

(19 Aug '14, 12:03) Breck Carter
Comment Text Removed
1

You, me, Volker :)...

alt text

(19 Aug '14, 12:10) Breck Carter

Well, if "Untergangsstimmung" is a German word that has made its way into current English, then "Erbsenzählerei" (meaning pedantry) may follow, as well:)

Volker from SAPland:)

(19 Aug '14, 12:14) Volker Barth
2

alt text

(19 Aug '14, 12:23) Breck Carter
Comment Text Removed

I'm sorry, I asked simplify about this strange behavior (dbbackup -xo).
I understood we must to use dbbackup -r option when truncate transaction log which used mirroring.

(20 Aug '14, 04:12) Tsuyoshi Yagi
Replies hidden

If you think the "strange behaviour" you have observed w.r.t. the mirror log file should be documented, then I'd suggest that you add a comment on the according topic in the DCX page, it's easy to do:) - If it has puzzled you, it may puzzle others, so a comment would seem worthwhile.


At least that's my personal view on DCX comments when features have been a surprise to me, even when someone has explained their reason, as Jack and Breck have done here...

(20 Aug '14, 05:37) Volker Barth
showing 5 of 14 show all flat view
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:

×31
×27

question asked: 15 Aug '14, 01:46

question was seen: 828 times

last updated: 20 Aug '14, 05:57