Last weekend I had a fun with installing service pack 2 on SQL Server 2008R2 (upgrade from version 10.50.2500 to 10.50.4000). After upgrade, I was experienced with the following issue:
Login failed for user user_name. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
All symptoms were reflected as an error 18401. As I realised, there are many reason why one of the scripts was not able to complete task. And the result was the same – upgrade mode prevented me connecting to the patched instance. I tried many workarounds found in many white papers, blogs and forums. The only way to connect the instance was applying the flag 902 as described in KB 2163980. But it did not resolve my problem completely.
What I missed was the fact that instance had a couple of user databases in suspended mode. Well, I thought it mightn’t be a problem for service pack. But it was :(. I shouldn’t postpone fixing these databases as deferred task. That was my mistake.
For the last 15 years I do not remember that kind of problems. But it looks like a trend – modern service packs are becoming more complex and, as a result, more sensitive to your environments.
Just put one more check box in your list before applying service pack – to fix or disconnect corrupted databases.