|
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? |
|
Attempting to start the database in read-only mode will tell you this information:
... but you will end up starting the database in read-only mode if it doesn't require recovery. |
|
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. 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
|
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 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.
DB_PROPERTY ( 'CheckpointUrgency' ) and 'RecoveryUrgency' are available when the database is up and running... which doesn't meet the requirement "without starting the database" :)
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:))