Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 23 Jun '23, 09:30

Martin's gravatar image

Martin
9.1k130170257
accept rate: 14%


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.

permanent link

answered 23 Jun '23, 09:56

John%20Smirnios's gravatar image

John Smirnios
12.0k396166
accept rate: 37%

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.

(23 Jun '23, 10:05) Martin
Replies hidden

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

(23 Jun '23, 10:16) Volker Barth
2

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.

(23 Jun '23, 10:23) John Smirnios
Comment Text Removed

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

(23 Jun '23, 10:36) Martin

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.

(23 Jun '23, 11:29) John Smirnios

We tried -im v but it requested a license

(26 Jun '23, 04:25) Martin

No assertion, it is treated as "internal error"

(26 Jun '23, 04:39) Martin
2

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

(26 Jun '23, 04:41) Martin
showing 1 of 8 show all flat view

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...

permanent link

answered 23 Jun '23, 09:51

Volker%20Barth's gravatar image

Volker Barth
40.4k365553825
accept rate: 34%

edited 23 Jun '23, 09:53

lol, both my own questions ...

(23 Jun '23, 10:08) Martin

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

permanent link

answered 18 Sep '23, 08:53

gulshan212's gravatar image

gulshan212
-13113
accept rate: 0%

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:

×261
×29

question asked: 23 Jun '23, 09:30

question was seen: 529 times

last updated: 18 Sep '23, 08:53