xml地图|网站地图|网站标签 [设为首页] [加入收藏]

热门视频

当前位置:美高梅游戏网站 > 热门视频 > The log scan number (620023:3702:1) passed to log scan in da

The log scan number (620023:3702:1) passed to log scan in da

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-11-12 10:07

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

今天查看Job的History,发现Job 运行失败,错误信息是:“The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'.”

 

错误消息表明:数据库的事务日志文件空间耗尽,log 文件不能再存储新的transaction log。

 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.

SQL Server将事务日志文件在逻辑上划分为多个VLF(Virtual Log Files),将这些VLF组成一个的环形结构,以VLF为重用单元。如果一个VLF 中存在Active Transaction,那么该VLF就不能被截断和重用。如果事务日志文件没有可用的VLF,那么SQL Server就不能处理新增的事务,并抛出事务日志文件耗尽的错误消息。

 

那为什么Active Transaction 会导致事务日志文件耗尽?

 

1,如果数据库的事务日志文件太大,将整个Disk Space耗尽,那么就要考虑是什么原因造成事务日志文件大量增长,定期做事务日志备份能够截断事务日志文件。

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

2,如果数据库的事务日志文件本身不是很大,可能的原因是SQL Server 无法为事务日志文件分配Disk Space。

 

3,查看数据库中活动的事务,如果是由于一个事务运行时间太长,没有关闭,导致事务日志的VLF不能重用,那么必须修改应用程序。

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.

 

如果数据库中某一个 Transaction 运行的时间太长,导致其他transaction虽然被commint,但是其占用的VLF仍然被标记为Active,不能被truncate和reuse,当log文件中没有可用的VLF,而SQL Server又要处理新增的Transaction时,SQL Server就会报错。

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,很多内部信息不太清楚(例如,是否出现异常),官方也没有找到很详细的介绍这个错误的相关资料。在此记录一下。

 

 

 

 

参考资料:

 

step1,查看事务日志文件的大小

查看日志文件的 size_gb 和 max_size_gb 字段,发现该事务日志文件的大小没有达到最大值,并且事务日志文件占用的Disk Space并不是很大,我猜想,很可能是日志文件所在的Disk Space 被使用殆尽,没有剩余的free space。

select db.name as database_name,
    db.is_auto_shrink_on,
    db.recovery_model_desc,
    mf.file_id,
    mf.type_desc,
    mf.name as logic_file_name,
    mf.size*8/1024/1024 as size_gb,
    mf.physical_name,
    iif(mf.max_size=-1,-1,mf.max_size*8/1024/1024) as max_size_gb,
    mf.growth,
    mf.is_percent_growth,
    mf.state_desc
from sys.databases db 
inner join sys.master_files mf 
    on db.database_id=mf.database_id
where mf.size*8/1024/1024>1  -- GB
    and db.name='database name'
    and mf.type=0
order by size_gb desc

step2,查看Disk的Free Space

查询结果显示,D盘空间仅仅剩下9MB,正是事务日志文件所在的Disk。

exec sys.xp_fixeddrives

图片 1

step3,Disk Space 用尽,必须想办法将大的数据文件压缩,或者将事务日志文件截断。

由于数据库的恢复模式是simple,会自动截断事务日志文件,因此,最大的可能是disk space耗尽。

1,查看数据库空间的使用情况

exec sys.sp_spaceused

unallocated space 空闲很大,必须压缩数据库,以释放disk space

本文由美高梅游戏网站发布于热门视频,转载请注明出处:The log scan number (620023:3702:1) passed to log scan in da

关键词:

上一篇:没有了

下一篇:没有了