记一次处理日志文件过大问题的解决过程(SQL Server)

问题描述: 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()无返回信息,日志文件可正常收缩至正常大小.问题解决.