Database recovery can be a time consuming task, so does any tool exists, which give you information about the need to recover and also possibly about the extent of recovery which will be performed, like last checkpoint in relation to transaction logfile or so, without starting the database?

asked 26 Jul '13, 07:09

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

A rough answer to the question, "Does this database need recovery?" might be given by the dbsrvxx -o console log. If it ends with something like this, then the answer is probably "no", otherwise it is probably "yes":

I. 07/26 07:57:54. Starting checkpoint of "ddd10" (ddd10.db) at Fri Jul 26 2013 07:57
I. 07/26 07:57:54. Finished checkpoint of "ddd10" (ddd10.db) at Fri Jul 26 2013 07:57
I. 07/26 07:57:54. Database server stopped at Fri Jul 26 2013 07:57
(26 Jul '13, 07:48) Breck Carter

I remember previous versions having console message like "checkpoint urgency" or similar, but I haven't seen this lately anymore. It's possible that these engines had parameters -gc or -gr set.
Cf. How the database server decides when to checkpoint

Found out "Checkpoint Urgency and Recovery Urgency wil be displayed by dbconsole, also available via sa_db_properties (CheckpointUrgency and RecoveryUrgency). Don't know, if that helps wrt the question.

(26 Jul '13, 08:57) Reimer Pods
Replies hidden

DB_PROPERTY ( 'CheckpointUrgency' ) and 'RecoveryUrgency' are available when the database is up and running... which doesn't meet the requirement "without starting the database" :)

(27 Jul '13, 11:58) Breck Carter

Would any of the "CheckpointXyz" or "ChkptXyz" or the "RollbackLogPages" database properties tell anything relevant about the state of the recovery need before stopping the database?

(I'm aware that even if such properties would exist, that request would not be safe in a multi-user database, as during the query other transactions could still produce operations that would modify the recovery state... - do I ask for a DATABASE STOP event:))

(29 Jul '13, 05:44) Volker Barth

Attempting to start the database in read-only mode will tell you this information:

dbsrvX <database.db> -r

**Error** - Database cannot be started -- <filename> cannot be started read-only because it requires recovery.

... but you will end up starting the database in read-only mode if it doesn't require recovery.

permanent link

answered 26 Jul '13, 09:45

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

If you try to disable the transaction log (dblog -n foo.db) for a database that requires recovery, it will fail with the message "Database quit abnormally last time it was used. Database recovery must be performed before proceeding."

However, you MUST remember to enable the log again if you successfully disable it!

Jeff's suggestion of using read-only mode is probably safer.

permanent link

answered 27 Jul '13, 07:53

John%20Smirnios's gravatar image

John Smirnios
8.7k377107
accept rate: 40%

So there's no "database flag" (in the header page) indicating that - in a way, say a new DBTools API function could interpret that?

(Note: I don't ask for such a suggestion, it's just out of curiosity...)

(29 Jul '13, 04:05) Volker Barth
Replies hidden
2

There's no API available to make that determination. It actually gets a bit more complex than I originally thought too. A successful invocation of "dblog -n foo.db" really means "the transaction log is not needed for recovery". There are at least three separate indications of "needs recovery": checkpoint log, undo log(s), and redo log. I believe the read-only mode will check all three.

(29 Jul '13, 14:39) John Smirnios
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:

×22

question asked: 26 Jul '13, 07:09

question was seen: 881 times

last updated: 29 Jul '13, 14:39