Based on that question, I would suggest that the error handling/message display of the ALTER DATABASE UPGRADE statement should be improved.

In my current case, the statement fails with an error message. However, it does not say that the database has become invalid by this - nor does it state one has to shutdown the database afterwards (in which case I would have noticed the problem immediately). It appears just like any other SQL statement that has failed - and those do not make the database file invalid. FWIW, the behaviour is identical with dbisqlc and DBISQL.

In that particular case, re-conneting the DBA user and retrying the statement seemed to work (there were no error message), and the database seemed to work fine - and the automated maintenance plans were executed as usual - and overwrote the previous backup because the running database was successfully validated.

But in the end, the database could not be restarted.

I guess that particular problem (only occuring with the PROCEDURE ON clause) might be a bug - however I'm refering to the general behaviour.

Note, I am aware that the documentation to ALTER DATABASE UPGRADE does state one should do a backup before and should restart the database after executing the statement. (Yes, I had better read this before...)

But DBUPGRAD and SC's Upgrade Wizard are explicit about these warnings, and the ALTER DATABASE statement is not. IMHO, it should be as well.

asked 29 Jun '11, 04:25

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
accept rate: 32%

With this explanation you should answer your other question by yourself ;-) Great that you identified the cause.

(29 Jun '11, 05:33) Martin
Replies hidden

Well, I guess I do understand now what has happened (which is a good thing by itself) - the (other) question is still open to clarify why the upgrade has failed (and why it seemed to work after a second attempt...).

But in general I feel better know - a database becoming invalid "by accident" would be faaaaaaaaaaar worse:)

(29 Jun '11, 05:40) Volker Barth

Yes Volker, I agree with you here. I will open an enhancement request and get ALTER DATABASE changed to always put out a "restart database" message.

permanent link

answered 29 Jun '11, 07:40

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

Maybe it should just shut down the database automatically...

(04 Jul '11, 09:25) John Smirnios
Replies hidden

Hmm, I ran it on an already running database started as a service... Obviously the connected DBA user would have had the permission to issue a STOP DATABASE statement - but not when connected to that very database:)

(04 Jul '11, 09:30) Volker Barth

Empirical evidence seems to suggest that you can shut down the database to which you are connected. Maybe for ALTER DATABASE UPGRADE the database should be shut down and then restarted too (you would, of course, lose your connection to it though).

(05 Jul '11, 16:48) John Smirnios

Poor me - I'd trusted the docs:)

(06 Jul '11, 04:07) Volker Barth
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:

×415
×107
×54
×41

question asked: 29 Jun '11, 04:25

question was seen: 2,259 times

last updated: 06 Jul '11, 04:07