Server is in script upgrade mode. Only Administrators can connect at this time Error: 18401
The above error can generate after applying SQL Server Service pack. The problem arises when you apply a SP\CU and after restarting the server when you try to connect to SQL you get above error message.
Cause: This happens sometime when Service Pack installation completes successfully but certain scripts like sqlagentxxx_msdb_upgrade.sq mostly in the Service Pack will be applied only after the SQL Server service starts the next time.
Script upgrade means that when SQL is restarted for the first time after the application of the patch, the upgrade scripts are run against each system databases to upgrade the system objects. During this process, SQL Server attempts to create this mdf file in the default data location, and if the path is not available, then we get this error. Most of the time, it’s a result of the data having been moved to a different folder, and the original Default Data path being no longer available. The default data path can be checked from the following registry key (for a default SQL 2008 instance):
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer
Workaround and Resolution
- Wait for few more minutes after start of SQL Server Services, as it takes a little time to apply the script .Keep an eye on any error message in Windows event log and SQL Server error log. If everything is good then you would be able to connect.
- In one of the scenario the default location of the database was changed since its installation but the registry entry was still pointing to the default path folder whose structure was deleted from the disk. In this case you will get below error logged in the error log
Here SQL Server is picking up the default data drive location from Registry and trying to create temporary .mdf file which is necessary to complete the upgrade.
FIX:
To quickly resolve this issue we need to look into the registry path containing the default data path location and change it to existing one. This approach is more practical as we will not face the same issue while next patching activity otherwise you can create the missing folder structure on the drive.
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer
Check the value for the String Entry named “DefaultData” and change the location to existing one .Restart the SQL Services and monitor Errorlog file.
Another scenario is when you have Utility control Point enabled in SQL Server 2008 R2 and Agent XPs disabled. This issue is fixed in SQL Server 2008 R2 CU2 but I included this here if in case this is the one you are facing.
This component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.
FIX:
Below are the steps you need to follow in order to fix above error message,
Enable Trace Flag 902 to disable the script execution. Restart the SQL Services.
Make sure that SQL Agent Services are in stopped state.
Connect to SSMS and execute below commands
EXEC sp_configure ‘show advanced’, 1;
RECONFIGURE;
EXEC sp_configure ‘allow updates’, 0;
RECONFIGURE;
EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO
Stop SQL Services and remove the trace flag 902. Start the services.
There could be several other reasons for the same.
http://support.microsoft.com/kb/2163980
coresql.in 