澳门在线威尼斯官方 > 电脑数据库 > 威尼斯澳门在线:Shrink不能收缩Log

原标题:威尼斯澳门在线:Shrink不能收缩Log

浏览次数:97 时间:2019-09-07

前几日一台SQL Server 二零零六陆风X82的数据库在晚上5点多抛出下边告警音讯:

威尼斯澳门在线 ,接纳Backup成立测验碰着之后,开采testdb的Log File过大,到达400GB,由于测验景况实际上无需这么大的Log Space,占用400GB的Disk Space实在荒凉Disk Resource,于是利用DBCC Shrink收缩Log File:

 

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

 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.

命名试行到位以往,开掘还大概有300多GB,实际Log File占用的空间的比例特别低,0.000428%

 

DBCC SQLPERF(LOGSPACE)

 

由于test db的恢复生机形式是Simple,并且没有active user,最大的或许是db的Trasaction log被标记为Replication,使用以下函数总括,发掘有雅量的log未被LogReader读取。

   乍一看,还认为数据库损坏了(data corruption),不过在做完DBCC CHECKDB后,开掘实际上数据库其实是完美的。那么分明是跟Replication有关。可是在物色了有关资料,仅仅在The process could not execute ‘sp_repldone/sp_replcounters” 那篇博客中找到了临近错误的素材:

select count(0)
from sys.fn_dblog(null,null) f
where f.Description ='REPLICATE'

 

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

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.

 

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.

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,比较多里边新闻不太知道(比方,是或不是出现分外),官方也未有找到很详细的介绍这些颠倒是非的相关材料。在此记录一下。

 

 

 

 

参照他事他说加以考察资料:

 

exec sys.sp_repltrans

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

由于testdb是接纳backup还原的测量试验数据库,未有在master中注册为Publisher database,必需设置 database 为publish,表示 Database can be used for other types of publications.

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:威尼斯澳门在线:Shrink不能收缩Log

关键词:

上一篇:没有了

下一篇:没有了