This is a follow-up to this question having narrowed things down a bit and excluded a few red herrings.

What can prevent a database from being opened in read-only mode that won't stop it from being opened normally?

The situation here is that an online backup is being done weekly, then the backed-up log is being applied to the backed-up database file successfully - and reporting "Recovery complete" afterwards. We are then applying periodic log backups to that database during the course of each day. Each night we start the (backed-up) database in read-only mode and validate it. Then we carry on applying logs etc.

We are finding that occasionally (one in ten times perhaps) we cannot start the database in read-only mode (whether directly, dbsrv16 ..., or indirectly dbvalid ...). In each case the error we get is:

SQL error (-82) -- Unable to start specified database: unable to start database "f:\databases\iqx\pears.db"

No other information is given as to why the database cannot start, either in the server log or in the Windows Event log. We tried adding the -z parameter in case that gave more information, but it doesn't.

One can then find that after further logs are applied the database can then be started read-only (and validated) without a problem.

The help docs say:

Databases that require recovery cannot be started in read-only mode. For example, database files created using an online backup cannot be started in read-only mode if there were any open transactions when the backup was started, since these transactions would require recovery when the backup copy is started

In this case though, the last thing that happened to the database (ie applying logs) before trying to start it in read-only mode concludes with "Recovery complete" - so presumably the database can't "require recovery".

In answers to my previous question there was the suggestion that some environmental change could be responsible, but we have tried to eliminate that possibility. First by always specifying -x none to avoid TCP/IP issues and secondly we were able to run a test starting the database in normal (ie not read_only) mode successfully. When we did that all other parameters were the same and no additional information relating to recovery appeared in the log (we couldn't then of course continue to use the database to apply logs to as the offset had changed).

Any suggestions welcome!

v16.0.0.2076

asked 08 Feb '16, 09:57

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%


It's likley because of transactions that span multiple transaction logs.

You connect to the database, make a modification to the database, and leave the transaction open. dbbackup -r -t -n then runs, and in your backup directory you end up with a transaction log "160208AC.LOG" that does not have a COMMIT in it for a transaction that was started in the log. After the backup, you commit the transaction, and then run dbbackup -r -t -n again, generating "160208AD.LOG" which includes the COMMIT for the transaction started in "160208AC.LOG".

During recovery, you do the following :

dbsrv16 -a 160208AA.LOG recovery.db
dbsrv16 -a 160208AB.LOG recovery.db
dbsrv16 -a 160208AC.LOG recovery.db

Recovery showed no errors in any of the steps, because nothing bad happened. However, in order to be able to start the database it will require recovery, because the current version of the recovered database after applying 160208AC.LOG has a ROLLBACK log associated with it. If you attempt to start the database in read-only mode, you will get error -82, since recovery is needed. If you were then to apply the next transaction log (which contains the COMMIT of the transaction in 160208AC.LOG), you could then start the database in read-only mode, since there would no longer be a ROLLBACK log in the current version of the recovered database.

If this is a big issue for you, conisder using the -wa switch on the dbbackup command to ensure there are no open transactions in the database when the log is renamed.

Reg

permanent link

answered 08 Feb '16, 12:10

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

1

So I guess using "dbsrv16 -ad MyLogDirectory" to apply all existing logs in one go might help to reduce the chance of closing the database with an open transaction (i.e. in your sample it should succeed as the last log has no open transaction) but would not guarantee to prevent that, right?

(08 Feb '16, 12:22) Volker Barth
Comment Text Removed
1

Many thanks Reg - that is a very clear explanation, and makes perfect sense.

With -wa I'm not entirely clear whether it just excludes open transactions or does it wait until there are no open transactions? If it's the latter I'm concerned that if we have a lot (over a thousand in this case) of users bashing away 24 x 7 - will it ever be able to finish?

(08 Feb '16, 13:34) Justin Willey
2

Your concern over -wa is legitimate, as dbbackup will wait until there are no open transactions to rename the log. In theory, it's possible that the process will never finish but based on your orginal post, since the problem you only happens about one out of every ten times, that tells me that 90% of the time, there are no open transactions when dbbackup renames the log. I think it's unlikely (but not impossible) given that information that dbbackup would wait forever.

You need to decide what the greater risk is in your environment. Is it the risk of dbbackup possibly never finishing or the risk of not being able to validate the recovery process after this backup because of an open transaction when the log was renamed?

Reg

(08 Feb '16, 15:19) Reg Domaratzki
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:

×260
×16
×3

question asked: 08 Feb '16, 09:57

question was seen: 2,717 times

last updated: 08 Feb '16, 15:19