由于Replication,DBCC Shrink不能收缩Log File必发bifa88手机客服端

昨天一台SQL
Server 2008R2的数据库在凌晨5点多抛出下面告警信息:

注册成功之后,使用 sp_repldone,将所有的Transaction Log
标记为Distributed。

 The log scan
number (620023:3702:1) passed to log scan in database ‘xxxx’ is not
valid. This error may indicate data corruption or that the log file
(.ldf) does not match the data file (.mdf). If this error occurred
during replication, re-create the publication. Otherwise, restore from
backup if the problem results in a failure during startup.

sp_repltrans
(Transact-SQL)
.aspx)

 

exec sys.sp_repltrans

 

由于test db的还原模式是Simple,并且没有active
user,最大的可能性是db的Trasaction
log被标记为Replication,使用以下函数统计,发现有大量的log未被LogReader读取。

How to fix this

 

  • Ensure database consistency by running DBCC CHECKDB on the
    database. 

  • If an old backup was restored on top of published database then
    use sp_replrestart

  • If going back to the most recent transaction log backup is not an
    option then execute sp_replrestart  on
    publisher in published database. This stored procedure is used when
    the highest log sequence number (LSN) value at the Distributor does
    match the highest LSN value at the Publisher.

  • This stored procedure will insert compensating LSNs (No Operation)
    in the publisher database log file till one the compensating LSN
    becomes more than the highest distributed LSN in distribution
    database for this published database. After this it inserts this new
    high LSN in the msrepl_transactions table in the distribution
    database and executes sp_repldone on published database to update
    the internal structures to mark a new starting point for log reader
    agent.

  • Ensure that the log reader agent is stopped and there is no incoming
    transactions on the published database, when this SP is executed.

  • Since transactions may have been lost, we recommend to reinitialize
    the subscriber(s) and/or recreate publication/subscription(s).  For
    large databases consider using “Initialize from Backup” as discussed
    in SQL Book Online.

 

但是在这个案例当中,
数据库既没有损坏,也没有还原过。 只能是Replication出现了错误,但是在SQL
Server的Replication中又没有找到相关错误信息,本身这个是AWS的DMS自动生成的Replication,很多内部信息不太清楚(例如,是否出现异常),官方也没有找到很详细的介绍这个错误的相关资料。在此记录一下。

 

 

 

 

参考资料:

 

https://blogs.msdn.microsoft.com/repltalk/2010/02/19/the-process-could-not-execute-sp\_repldonesp\_replcounters/

DBCC SQLPERF(LOGSPACE)
Common Causes

 

  • The last LSN in Transaction Log is less than what the LSN Log Reader
    is trying to find. An old backup may have been restored on top of
    Published Database. After the restore, the new Transaction Log
    doesn’t contain the data now distributor & subscriber(s) have.

  • Database corruption.

 

 

 

When xactid is NULL, xact_seqno is
NULL, and reset is 1, all replicated transactions in the log are
marked as distributed. This is useful when there are replicated
transactions in the transaction log that are no longer valid and you
want to truncate the log,

  
乍一看,还以为数据库损坏了(data corruption),但是在做完DBCC
CHECKDB后,发现其实数据库其实是完好无损的。那么肯定是跟Replication有关。但是在搜索了相关资料,仅仅在The
process could not execute
‘sp_repldone/sp_replcounters”

这篇博客中找到了类似错误的资料:

exec sys.sp_replicationdboption
        @dbname = N'testdb', 
        @optname = N'publish', 
        @value = N'true' 

 

sp_repltrans returns a result set of all
the transactions in the publication database transaction log that are
marked for replication but have not been marked as distributed.

由于testdb是使用backup还原的测试数据库,没有在master中注册为Publisher
database,必须设置 database 为publish,表示 Database can be used for
other types of publications.

使用Backup创建测试环境之后,发现testdb的Log
File过大,达到400GB,由于测试环境实际上不需要这么大的Log
Space,占用400GB的Disk Space实在浪费Disk Resource,于是使用DBCC
Shrink收缩Log File:

在Publisher database中,使用 sp_repltrans
查看没有被LogReader标记为Distributed的Transaction。

sp_replicationdboption
(Transact-SQL)
.aspx)

Unable to execute
procedure. The database is not published. Execute the procedure in a
database that is published for replication.

sp_repldone
(Transact-SQL)
.aspx)

最后,使用DBCC ShrinkFile命令,Transaction Log File收缩完成。

EXEC sys.sp_repldone 
        @xactid = NULL, 
        @xact_segno = NULL, 
        @numtrans = 0,     
        @time = 0, 
        @reset = 1  
select count(0)
from sys.fn_dblog(null,null) f
where f.Description ='REPLICATE'

参考doc:

命名执行完成之后,发现还有300多GB,实际Log
File占用的空间的百分比十分低,0.000428%

dbcc shrinkfile(testdb_log_5,10240,notruncate)
dbcc shrinkfile(testdb_log_5,10240,truncateonly)

sp_repldone updates the record that
identifies the last distributed transaction of the server.