Yesterday I replied to a question in which the user was not able to connect to the SQL Server 2008 instance after installing Service Pack 2. The error message is
Login failed for user ‘LoginName’
Reason: Server is in script upgrade mode.
Only administrator can connect at this time.
I was able to quickly respond to this question because I had faced this situation when I had installed SQL Server 2008 SP2 on my VM. Here is what I had done during the installation of SP2.
- SQL Server service was stopped prior to SP2 installation (this service is in manual mode in my VM, hence I forgot to start it)
- SP2 was successfully installed on the instance
- I started the SQL Server service
- Immediately I launched SSMS to connect to the instance and I got the above error.
This issue happens because the SQL Server service was stopped when the SP2 installation was in progress. Service Pack installation completes successfully but certain scripts (most of the times sqlagent100_msdb_upgrade.sql) in the Service Pack will be applied only after the SQL Server service starts the next time. Hence when I started the SQL Server service, these scripts were still being applied to the instance and I was trying to connect to the instance during that time. Hence the error message.
What is the resolution? Just wait! Wait for few minutes after the SP is installed and you start the SQL Server service for the first time. Once these scripts are applied to the instance, all the users will be able to connect to the instance normally.
There is already a BUG reported for this issue and it is under “consideration”. Even I feel that the installer should finish its work before reporting that it is “successful”.
I have seen many DBAs stopping the SQL Server services before applying any Service Packs/hotfixes. In fact there is no need to do it. The Service Pack installer will take care of stopping and starting the instance as and when required. This issue is a reason for not stopping the service before applying the SP. Even though there is not much difference, for sure it will prevent a few anxious moments while trying to connect to the instance for the first after installing the SP