SQL2012升级sp2失败-错误3930解决办法

SQL2012升级SP2安装失败，服务无法启动，错误信息如下：

-------------------------------------------------------

Error: 912, Severity: 21, State: 2.

Script level upgrade for database ‘master‘failed because upgrade step ‘msdb110_upgrade.sql‘encountered error 3930, state 1, severity 16. This is a serious errorcondition which might interfere with regular operation and the database will betaken offline. If the error happened during upgrade of the ‘master‘ database,it will prevent the entire SQL Server instance from starting. Examine theprevious errorlog entries for errors, take the appropriate corrective actionsand re-start the database so that the script upgrade steps run to completion.

-------------------------------------------------------

查看错误日志记录，从出错地方截取:

-------------------------------------------------------

2014-12-18 09:57:24.15 spid5s      Could not open database [system]. Replication settingsand system objects could not be upgraded. If the database is used forreplication, run sp_vupgrade_replication in the [master] database when thedatabase is available.

2014-12-18 09:57:24.21 spid5s      Upgrading subscription settings andsystem objects in database [Air3_Base].

2014-12-18 09:57:24.35 spid5s      Index cannot be created on object‘MSreplication_subscriptions‘ because the object is not a user table or view.

2014-12-18 09:57:24.35 spid5s      Error executing sp_vupgrade_replication.

2014-12-18 09:57:24.35 spid5s      Saving upgrade script status to ‘SOFTWARE\Microsoft\MSSQLServer\Replication\Setup‘.

2014-12-18 09:57:24.35 spid5s      Saved upgrade script status successfully.

2014-12-18 09:57:24.35 spid5s      Database ‘master‘ is upgrading script‘upgrade_ucp_cmdw_discovery.sql‘ from level 184551476 to level 184554434.

2014-12-18 09:57:24.81 spid5s      Database ‘master‘ is upgrading script‘msdb110_upgrade.sql‘ from level 184551476 to level 184554434.
-------------------------------------------------------

Could not open database [system] 为了避免数据库服务重启短信警报,打补丁前将该库offline。尝试online后，依然无法升级成功，该错误非致命。

继续尝试

MSreplication_subscriptions 该对象是为复制创建的系统表，在实例查找该对象，发现部分订阅库存在该系统表的同义词，所有复制相关系统表的同义词脚本如下：
USE [XX]

GO

/******Object:  Synonym[dbo].[MSreplication_objects]    ScriptDate: 2014/12/18 11:01:06 ******/

CREATE SYNONYM [dbo].[MSreplication_objects] FOR[YY].[dbo].[MSreplication_objects]

GO

/******Object:  Synonym[dbo].[MSreplication_subscriptions]   Script Date: 2014/12/18 11:01:06 ******/

CREATE SYNONYM [dbo].[MSreplication_subscriptions] FOR [YY].[dbo].[MSreplication_subscriptions]

GO

/******Object:  Synonym[dbo].[MSsnapshotdeliveryprogress]   Script Date: 2014/12/18 11:01:06 ******/

CREATE SYNONYM [dbo].[MSsnapshotdeliveryprogress] FOR [YY].[dbo].[MSsnapshotdeliveryprogress]

GO

/******Object:  Synonym[dbo].[MSsubscription_agents]    ScriptDate: 2014/12/18 11:01:06 ******/

CREATE SYNONYM [dbo].[MSsubscription_agents] FOR[YY].[dbo].[MSsubscription_agents]

GO

将这些同义词删除后，升级SP2成功。

按照错误日志的描述，原因应该是：数据库在升级SP2时将这些同义词误认为系统表，为这些同义词创建索引失败导致SP2升级失败。
解决办法：删除这些为系统表创建的同义词，升级成功后再创建回去。

总结：发生此类错误，第一时间去查看错误日志，按照出现的错误一条一条解决，不要被报错信息误导。

本文出自 “杨金澎” 博客，请务必保留此出处http://jpstar.blog.51cto.com/5493713/1591304