ALTER DATABASE UPGRADE should recommend to restart the database

[VolkerBarth]

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.

[MCMartin]

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

[VolkerBarth]

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:)

[Former Member · Accepted solution]

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.

[johnsmirnios · Accepted solution]

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

[VolkerBarth · Accepted solution]

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:)

[johnsmirnios · Accepted solution]

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

[VolkerBarth · Accepted solution]

Poor me - I'd trusted the docs:)