Server is in script upgrade mode. Only administrator can connect at this time

Posted on April 20, 2012 by BjTechNews in SQL // 0 Comments

So I was placed in charge of two things to do on the SQL 2008 server.

Update to SQL Server 2008 Service Pack 1
Add the Full Text Search Feature

After all was done, I was to quick on the draw and started the SQL Server Management Studio to check if everything went well and I got this nasty looking error:

First thing, that came to mind is like WHAT!!! Script upgrade mode? Only admin can connect at this time? Something went wrong with the Service Pack 1 installation, I started to freak out because this was our database.

I closed SQL Server Management Studio and went back to my notes that I followed:

SQL Server service was stopped prior to SP1 installation
SP1 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. [Ahhh!!!!]

I started to do some research and the reason why this was happening because the SQL Server service was stopped when the SP1 installation was in progress. Service Pack installation completes successfully, but certain scripts (most of the times sqlagentxxx_msdb_upgrade.sql) in the Service Pack will be applied only after the SQL Server service starts the next time. 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.

So what’s the resolution? be calm and grab a cup of coffee and just wait! Wait for few minutes after the service pack 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 at Microsoft Connect. I really feel that the installer should finish its work before reporting that it is “successful” not tell you that it’s done with the installation, but in reality it’s still doing something in the background.

For a first time DBA [Database Administrator], I have learned from other DBAs to stop the SQL Server services before applying any Service Packs/hotfixes [I guess that’s their best practice]. 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.