SQL Server 2008升级bug导致事务日志无法截断

【摘要】

最近遇到一个奇怪的问题，客户环境数据库的恢复模式为简单，但其日志文件一直无法收缩，即使重启数据库服务也一样。数据库版本为SQL Server2008 SP2，并且配置了订阅，复制订阅的状态是正常的。通过分析与查找资料后发现这是SQL Server 2008的升级bug，具体来说是复制订阅升级脚本的bug导致的问题，现将分析过程和解决方法分享出来，以便遇到类似问题时可以快速解决。
      本文转自IT培训机构-学领未来

【正文】

一     分析过程

1.1       同事反映客户那边有个数据库，日志文件一直无法收缩，用dbcc shrinkfile命令收缩时，返回结果是结尾的逻辑日志正在使用中，无法收缩。

1.2       第一反应就是做个日志备份，然后收缩。如果还不能收缩，再次做日志备份然后再次收缩，原因查看之前发的文章《SQLServer事务日志写入VLF顺序探究》

1.3       但是进一步消息是该库是简单模式，之前是完整模式，客户发现日志持续增长后改成简单模式，日志就限制住了，但是一直收缩不了。通过dbcc loginfo命令查看VLF使用情况，也确实都是在使用中，无法被截断。

1.4       第二个想法就是由于长时间未提交事务导致的，通过脚本查看用户活动事务的详细信息，未发现有任何活动事务。

select DTST.session_id as N'会话ID',db_name(DTDT.database_id) as N'数据库名',DTST.transaction_id   as N'事务ID',

(CASE S.status

when 'dormant' then N'SQL Server正在重置会话'

when 'running' then N'会话正在运行一个或多个批'

when 'background' then N'会话正在运行一个后台任务'

when 'rollback' then N'会话正在处理事务回滚'

when 'pending' then N'会话正在等待工作线程变为可用'

when 'runnable' then N'会话中的任务位于计划程序的可执行队列中'

when 'spinloop' then N'会话中的任务正在等待调节锁变为可用'

when 'suspended' then N'会话正在等待事件（如I/O）完成'

else S.status END) as N'会话状态',DTAT.transaction_begin_time as N'事务开始时间',

DATEDIFF(MINUTE,DTAT.transaction_begin_time,

GETDATE()) as N'事务未提交时间（分钟）',

S.open_tran as N'打开事务数量',

DES.last_request_start_time as N'最近事务开始时间',

DTDT.[Log Records],

DTDT.[Log Bytes Used],

DTDT.[Log Bytes Reserved],

EST.text as N'最近执行语句'

from sys.dm_tran_session_transactions as   DTST

join sys.dm_tran_active_transactions as DTAT

on DTST.transaction_id=DTAT.transaction_id

join (select transaction_id ,database_id,sum(database_transaction_log_record_count)   AS [Log Records],

sum(database_transaction_log_bytes_used) AS   [Log Bytes Used],

sum(database_transaction_log_bytes_reserved)   AS [Log Bytes Reserved]

from sys.dm_tran_database_transactions

where database_transaction_begin_time is not   null

group by transaction_id,database_id

) as DTDT

on DTDT.transaction_id=DTAT.transaction_id

join sys.dm_exec_sessions as DES

on DES.session_id=DTST.session_id

join sys.sysprocesses as S

on S.spid=DTST.session_id

outer apply   sys.dm_exec_sql_text(S.sql_handle) as EST

order by N'会话ID'

--where   DATEDIFF(MINUTE,DTAT.transaction_begin_time,GETDATE())>30

 

1.5       这时候感觉有点异常了，执行命令查看上次日志未截断原因，原因显示竟然为活动事务。

select name,log_reuse_wait_desc from   sys.databases

1.6       此时通过执行另一个命令，发现了一个系统的活动事务

DBCC OPENTRAN

结果为：

数据库'BaseDB'   的事务信息。

 

最早的活动事务:

    SPID (服务器进程ID): 6s

    UID (用户ID): -1

    名称           : tran_sp_MScreate_peer_tables

    LSN           : (944319:1228:1)

    开始时间     : 06 12 2018  8:52:25:673PM

    SID           : 0x01

 

已复制的事务信息:

        最早的分布式LSN     : (944641:41:18)

        最早的非分布式LSN : (0:0:0)

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

1.7       通过网上查找tran_sp_MScreate_peer_tables的相关信息，发现了这是一个bug，官方链接为

二     解决方法

根据官方文章，对于SQL Server 2012 SP1、SQL Server 2012、SQL Server 2008 R2 SP1、SQL Server 2008 SP3是有累计补丁CU的。而对于SQL Server 2008 SP3之前的版本，可以通过以下方法来处理。

2.1       停止SQL Server引擎服务和SQL 代理服务

2.2       启动SQL Server引擎服务，保持SQL 代理服务停止状态

2.3       查看SQL日志，是否有以下类似内容

·           spid7s Upgrading publication settings and system   objects in database [DBName].

·           spid7s Cannot perform this operation while   SQLServerAgent is starting. Try again later.

·           spid7s Error executing sp_vupgrade_replication.

·           spid7s Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.

·           spid7s Saved upgrade script status successfully.

 

2.4       也可以查看注册表HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCE\Replication\Setup 的值是否为1，表示升级成功。

2.5       再启动SQL代理服务即可。

2.6       保持SQL代理停止情况下启动SQL服务的实质是，复制升级脚本在SQL代理启动情况下无法完成；而启动SQL服务会自动执行sp_vupgrade_replication来升级复制，等其升级成功即可。