问题描述: SQL2008R2的一个DB, 兼容等级=100, 恢复模式=full, 数据量约30GB.
之前一直正常运行, 最近日志文件暴涨(日志文件66G), 日志备份作业执行很久没完成, 手工stop job.
手工执行backup log后执行DBCC SHRINKFILE()仍然无法收缩日志文件.
问题分析:
执行dbcc opentran()查看数据库的活动事务,发现spid=8的系统进程持有锁未释放,
查看SQL日志,SQL实例启动时spid 8有如下错误信息:
2015-07-27 13:02:41.44 spid8s Upgrading subscription settings and system objects in database [DB].
2015-07-27 13:02:41.52 spid8s Invalid object name 'MSreplication_subscriptions'.
2015-07-27 13:02:41.52 spid8s Error executing sp_vupgrade_replication.
2015-07-27 13:02:41.52 spid8s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
2015-07-27 13:02:41.52 spid8s Saved upgrade script status successfully.
2015-07-27 13:02:41.52 spid8s Database 'master' is upgrading script 'upgrade_ucp_cmdw_discovery.sql' from level 171050560 to level 171054960.
2015-07-27 13:02:41.53 spid8s ------------------------------------------------------
2015-07-27 13:02:41.53 spid8s Starting execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL
2015-07-27 13:02:41.53 spid8s ------------------------------------------------------
2015-07-27 13:02:41.54 spid8s The Utility MDW does not exist on this instance.
2015-07-27 13:02:41.54 spid8s User 'sa' is changing database script level entry 12 to a value of 2.
2015-07-27 13:02:41.54 spid8s Skipping the execution of instmdw.sql.
2015-07-27 13:02:41.54 spid8s ------------------------------------------------------
2015-07-27 13:02:41.54 spid8s execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL completed
2015-07-27 13:02:41.54 spid8s ------------------------------------------------------
2015-07-27 13:02:41.61 spid8s Recovery is complete. This is an informational message only. No user action is required.
2015-07-27 13:02:41.61 spid8s Launched startup procedure 'sp_MSrepl_startup'.
从日志看, 是Replication影响了, 手工执行sp_vupgrade_replication有报错, 查了DB里几个复制相关的系统表都是同义词:
MSsubscription_agents
MSreplication_objects
MSsubscription_properties
MSsnapshotdeliveryprogress
指向另一个DB的同名表, 但另一个DB已不存在了. 引用无效导致此系统存储过程执行报错.
根据MSDN文章
点击打开链接
按文章的方法,删掉这几个同义词, 找个正常的DB,拿到这4个系统表的建表脚本,到问题DB里来执行重建4个系统表.
然后在手工执行
exec sp_vupgrade_replication 结果正常!!! 然后测试在DB上新建发布和删除发布正常.
但执行dbcc opentran()还能看到那个spid=8的进程,
重启SQL实例后正常,
dbcc opentran()无返回信息,日志文件可正常收缩至正常大小.问题解决.