Is it possible to start a database read-only when it needs recovery?

[MCMartin]

Is it possible to start a database read-only when it needs recovery?

I have a DB which stopped with "max file size reached", so DB says it needs recovery when I try to start it. So my wish would be to start the DB read-only to do an unload. Any advice?

[johnsmirnios · Accepted solution]

Hitting the max file size means that you are either on an old FAT filesystem or your dbspace file is very large. On NTFS with a 4K database page size you should be able have dbspace files up to 1TB in size. If you are not using a filesystem that supports large files, you could copy everything to a filesystem that does.

Does the database fail to recover if you just start it in normal mode?

I don't think you can start a database in read-only mode if it needs recovery. You could probably use in-memory mode though with '-im nw' which shouldn't touch the file and modifications will be kept in memory. I vaguely recall that "nw" mode requires a different licence. There is also "-im v" mode (validation mode) which doesn't require a separate licence and allows writes by recovery but then is read-only. That said, we still "logically" allocate pages when using in-memory mode so you might still hit the same error.

[MCMartin · Accepted solution]

DB file size is currently 2TB, recovery says 100% complete but then fails still with "max file size" reached, leaving the DB in dirty mode.

[VolkerBarth · Accepted solution]

Is starting the database on an engine with a higher page size (so probably 16K or 32K) a workaround?

[johnsmirnios · Accepted solution]

Okay. You can try the in-memory modes but I suspect they won't help you. If they don't work we might have other options. First, I would recommend that you have a saved copy of the existing database files. Then you might proceed as follows:

1. Rename the transaction log to anything different and then start the server with "-f" on the command line. The server will then not replay transactions that were in the log after the last checkpoint. IIRC, the server will then shutdown automatically and you should then start it again but without -f. It might fail again with the same error at this point as it will now attempt to apply the undo logs. You can use dbtran to find the transactions that were committed after the last checkpoint.

2. If the above fails, start the server with -O (capital "o") on the command line. This will tell recovery not to apply the undo logs. Do not do this lightly: ideally do it on a copy of the database. If undo logs are not applied then your database might be inconsistent since partial transactions will effectively be committed.

[MCMartin · Accepted solution]

file copy needs some time, I will keep you updated, thanks so far for your suggestions

[johnsmirnios · Accepted solution]

Happy to help. BTW, are you actually getting assertion 201143 or 201144 ("page %x in file %d is beyond maximum file size %x")? I cannot find the actual message you provided. Please let me know which of the assertion failures you are seeing or the exact message.

[MCMartin · Accepted solution]

We tried -im v but it requested a license

[MCMartin · Accepted solution]

No assertion, it is treated as "internal error"

[MCMartin · Accepted solution]

Approach 1 worked, the database can be started now and we will do the unload, thanks for your help!!

[VolkerBarth]

According to that question and that one, I'd say "no" - but I would try to start it in read-only mode (probably with a copy of that database) anyway...

[MCMartin]

lol, both my own questions ...

[former_SQLA_member1694947]

Well, in generally in RDBMS it is generally not possible to start a database in a read only mode when it needs recovery. Whenever database required recovery it means that there is the problem with the consistancy of database which you can further perform by taking appropriate steps that you can find over Google. Thanks