Windows updates recently pulled down an update for SQL Server 2008 R2 which caused me some consternation this morning. I had kicked off my system and started working on some SSIS packages when I noticed my system starting to slow down. After a little while I lost connectivity with the SQL instance I was working on. Attempts to restart the server instance initially succeeded, but attempts to connect either through management studio or using sqlcmd failed with the message:
Login failed for user xxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.
No worries, thinks I, the deferred updates are running. Except they of course hit the same issue, and the service instance stopped again.
A look at the event logs told me that a part of the updates was failing with the message:
spid7s SQL Server blocked access to procedure ‘sys.xp_qv’ of component ‘Agent XPs’ because 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.
All good and well except for the fact that as the instance had stopped, I couldn’t connect to it in order to hit the master database and do the reconfiguration.
Ultimately this saved the day -> http://support.microsoft.com/kb/2163980
Using the –T902 flag when starting the instance halts the running of the deferred scripts, allowing you to connect to the instance, configure the master database appropriately, remove the flag and allow the updates to run successfully.
Wipe those tears away!
Matt