你的位置:首页 > 数据库

[数据库]SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败


有台数据库服务器(开发服务器),开发人员邮件告诉我,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分离。

clipboard

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目录下

 

image

 

4:执行命令net stop MSSQLSERVER关闭SQL服务。

clipboard[1]

 

5:从SQL Server Configuration Manager里面启动SQL SERVER服务。

 

6:附加刚才被分离的数据库SSISDB

    附加时出现下面错误,这个是因为Windows SERVER 2012的权限问题所导致,关掉SSMS后,要以管理员运行SSMS,附加成功。问题解决。

clipboard[2]

 

参考资料:

http://ayadshammout.com/2014/02/04/sql-2012-sp1-cu6-issue-with-ssis-db-in-availability-group/