SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败-阿里云开发者社区

SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败

2016-04-27 3100

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

简介：

有台数据库服务器（开发服务器），开发人员邮件告诉我，SSMS连接不了这台服务器，远程登录后，发现SQL SERVER的服务停止了，启动服务时报错，服务启动不了。检查错误日志发现下面一些信息

 
  2015-10-14 13:47:09.98 spid17s     The Service Broker endpoint is in disabled or stopped state. 
  2015-10-14 13:47:10.03 spid17s     The Database Mirroring endpoint is in disabled or stopped state. 
  2015-10-14 13:47:10.13 spid17s     Service Broker manager has started. 
  2015-10-14 13:47:11.98 spid7s      Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 201328592 to level 201330692. 
  2015-10-14 13:47:13.99 spid7s      Error: 942, Severity: 14, State: 4. 
  2015-10-14 13:47:13.99 spid7s      Database 'SSISDB' cannot be opened because it is offline. 
  2015-10-14 13:47:14.11 spid7s      Error: 912, Severity: 21, State: 2. 
  2015-10-14 13:47:14.11 spid7s      Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 942, state 4, severity 25. 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. 
  2015-10-14 13:47:14.14 spid7s      Error: 3417, Severity: 21, State: 3. 
  2015-10-14 13:47:14.14 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. 
  2015-10-14 13:47:14.14 spid7s      SQL Server shutdown has been initiated 
  2015-10-14 13:47:14.14 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. 
  2015-10-14 13:47:15.15 spid7s      Error: 25725, Severity: 16, State: 1. 
  2015-10-14 13:47:15.15 spid7s      An error occurred while trying to flush all running Extended Event sessions.  Some events may be lost. 
  2015-10-14 13:47:15.17 spid13s     The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) [ MSSQLSvc/GETNTXX.XXX.XXXX.com:1433 ] for the SQL Server service. Error: 0x2af9, state: 61. Administrator should deregister this SPN manually to avoid client authentication errors. 
  2015-10-14 13:47:15.17 spid13s     The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) [ MSSQLSvc/GETNTXX.XXX.XXXX.com:1433 ] for the SQL Server service. Error: 0x2af9, state: 61. Administrator should deregister this SPN manually to avoid client authentication errors. 
 

真是一头雾水，不知道是否同事做了什么操作（补丁升级之类）导致出现了数据库服务启动不了的错误。于是按照下面步骤进行了紧急修复处理：

1：启动 SQL Server 服务启跟踪标志902

一般使用命令： Net Start MSSQL$InstanceName /T902 如果是默认实例 Net Start MSSQLSERVER /T902

C:\Windows\system32>net start MSSQLSERVER /T902

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

2：启动SQL SERVER后，在本机使用SSMS连接数据库

如下所示，你会看到SSISDB处于脱机模式（Offline），先将数据库SSISDB分离。

3：找到 SSIS_hotfix_install.sql脚本并执行。

SSIS_hotfix_install.sql脚本一般位于安装目录 \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName \MSSQL\Install下，例如这台服务器位于C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install目录下