SQLServerWiki

“The Only Thing That Is Constant Is Change”

bypass script upgrade mode in SQL Server 2008 or later using trace flag -T902

Posted by database-wiki on September 30, 2011

Issue:

Installing Server pack or cumulative update to a clustered SQL Server 2008 instance or standalone instance won’t bring the instance online even after the patching process completes successfully.

The issue lies in the upgrade scripts that run as a part of patching process. When these script fail to complete successfully then the instance won’t come online.

This is applicable for both standalone and cluster also not only for SQL Server 2008 but also SQL Server 2008 R2.

Related Errors:

2011-08-11 14:02:11.86 spid7s
Error: 574, Severity: 16, State: 0.

2011-08-11 14:02:11.86 spid7s
CONFIG statement cannot be used inside a user transaction.

2011-08-11 14:02:11.86 spid7s
Error: 912, Severity: 21, State: 2.

2011-08-11 14:02:11.86 spid7s
Script level upgrade for database ‘master’ failed because upgrade step
‘sqlagent100_msdb_upgrade.sql’ encountered error 574, state 0, severity 16.
This is a serious error condition which might interfere with regular operation
and the database will be taken offline. If the error happened during upgrade of
the ‘master’ database, it will prevent the entire SQL Server instance from
starting. Examine the previous errorlog entries for errors, take the
appropriate corrective actions and re-start the database so that the script
upgrade steps run to completion.

2011-08-11 14:02:11.86 spid7s
Error: 3417, Severity: 21, State: 3.

2011-08-11 14:02:11.86 spid7s
Cannot recover the master database. SQL Server is unable to run. Restore master
from a full backup, repair it, or rebuild it. For more information about how to
rebuild the master database, see SQL Server Books Online.

……

……

2011-08-11 14:02:10.96 spid7s
A problem was encountered granting access to MSDB database for login ‘(null)’.
Make sure this login is provisioned with SQLServer and rerun
sqlagent_msdb_upgrade.sql

2011-08-11 14:02:10.96 spid7s
A problem was encountered granting access to MSDB database for login ‘(null)’.
Make sure this login is provisioned with SQLServer and rerun
sqlagent_msdb_upgrade.sql

……

……

2011-08-11 14:28:25.57
Logon       Error: 18401, Severity: 14, State: 1.

2011-08-11 14:28:25.57
Logon       Login failed for user
‘C****\!C*****’. Reason: Server is in script upgrade mode. Only
administrator can connect at this time. [CLIENT: <named pipe>]

Challenges you might face:

The instance wont stay online for long if the upgrade scripts are failing.

We need to use trace flag –T3608 this Prevents SQL Server from automatically starting and
recovering any database except the master database. Databases will be started and recovered when accessed.

So this avoids the script upgrade mode but making an admin connect to the instance might be challenging to proceed further with the troubleshooting. As other application might grab that connect.

Related Errors:

2011-08-11 13:52:44.45
Logon       Error: 18401, Severity: 14, State: 1.

2011-08-11 13:52:44.45
Logon       Login failed for user
‘C****\!C*****’. Reason: Server is in script upgrade mode. Only
administrator can connect at this time. [CLIENT: <named pipe>]

“Login failed for user
‘sa’. Reason: Server is in single user mode. Only one administrator can connect
at this time. (Microsoft SQL Server, Error: 18461)

Work Arround:

=> Start the SQL Server with trace flag -T902 which will by pass running the
upgrade scripts and will allow you to connect to the instance.

After connection in my scenario we had a orphaned user in the MSDB when we deleted it, the upgrade scripts completed successfully.

Example Scenario:

2011-08-11 14:02:10.95
spid7s      Granting login access’SAP*****’ to
msdb database…

2011-08-11 14:02:10.96
spid7s      A problem was encountered granting access
to MSDB database for login ‘(null)’. Make sure this login is provisioned with
SQLServer and rerun sqlagent_msdb_upgrade.sql

2011-08-11 14:02:10.96
spid7s      A problem was encountered granting access
to MSDB database for login ‘(null)’. Make sure this login is provisioned with
SQLServer and rerun sqlagent_msdb_upgrade.sql

2011-08-11 14:02:10.96
spid7s      

2011-08-11 14:02:10.96
spid7s      Adding user ‘SAP*****’ to
SQLAgentUserRole msdb role…

=> Looks like orphan user in the msdb.

=> We Started the SQL Server with trace flag -T902 which will bypass running the upgrade scripts.

=> SQL Server started successfully without running the upgrade scripts.

=> Checked if there are any orphaned users in msdb database and found SAP***** as an orphan user.

=> We deleted it and stop sql server and brought it online from cluster.

=> Script upgrade completed.

2011-08-11 14:28:24.22
spid7s      —————————————–

2011-08-11 14:28:24.22
spid7s      Execution of POSTINSTMSDB100.SQL complete

2011-08-11 14:28:24.22
spid7s      —————————————–

2011-08-11 14:28:24.23 spid7s
Upgrading Database Mail related objects…

2011-08-11 14:28:24.39 spid7s
Completed upgrade of Database Mail related objects…

2011-08-11 14:28:24.41 spid7s
Database ‘master’ is upgrading script ‘repl_master.sql’ from level 167776160 to
level 167776476.

2011-08-11 14:28:24.41 spid7s      Configuration
option ‘allow updates’ changed from 0 to 1. Run the RECONFIGURE statement to
install.

2011-08-11 14:28:24.41 spid7s
Configuration option ‘allow updates’ changed from 0 to 1. Run the RECONFIGURE
statement to install.

2011-08-11 14:28:24.41 spid7s
FILESTREAM: effective level = 0, configured level = 0, file system access share
name = ‘NW’.

2011-08-11 14:28:24.43 spid7s
Creating procedure sp_MSrepl_startup

2011-08-11 14:28:24.45 spid7s
Creating procedure sp_MScleanupmergepublisher

2011-08-11 14:28:24.51 spid7s
Configuration option ‘allow updates’ changed from 1 to 0. Run the RECONFIGURE
statement to install.

2011-08-11 14:28:24.51 spid7s
Configuration option ‘allow updates’ changed from 1 to 0. Run the RECONFIGURE
statement to install.

2011-08-11 14:28:24.51 spid7s
FILESTREAM: effective level = 0, configured level = 0, file system access share
name = ‘NW’.

2011-08-11 14:28:24.52 spid7s
Database ‘master’ is upgrading script ‘repl_upgrade.sql’ from level 167776160
to level 167776476.

2011-08-11 14:28:24.52 spid7s
Executing replication upgrade scripts.

2011-08-11 14:28:24.53 spid7s
Executing sp_vupgrade_replication.

2011-08-11 14:28:24.62 spid7s
Upgrading publication settings and system objects in database [TW3].

2011-08-11 14:28:25.10 spid7s
Upgrading subscription settings and system objects in database [TW3].

2011-08-11 14:28:25.57
Logon       Error: 18401, Severity: 14, State: 1.

2011-08-11 14:28:25.57
Logon       Login failed for user ‘C****\!C*****’.
Reason: Server is in script upgrade mode. Only administrator can connect at
this time. [CLIENT: <named pipe>]

2011-08-11 14:28:25.77 spid7s
Attempting to load library ‘xprepl.dll’ into memory. This is an informational
message only. No user action is required.

2011-08-11 14:28:25.80 spid7s
Using ‘xprepl.dll’ version ‘2007.100.1600’ to execute extended stored procedure
‘xp_repl_encrypt’. This is an informational message only; no user action is
required.

2011-08-11 14:28:25.81 spid7s
Attempting to copy article resolvers from SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL10.NW\Replication\ArticleResolver

2011-08-11 14:28:25.93 spid7s
sp_vupgrade_replication executed successfully

2011-08-11 14:28:25.93 spid7s
Saving upgrade script status to
‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup’.

2011-08-11 14:28:25.93 spid7s
Saved upgrade script status successfully.

2011-08-11 14:28:25.93
spid7s      Recovery is complete. This is an
informational message only. No user action is required.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: