当前位置: 永利皇宫463手机版 > 数据库 > 正文

Shrink不能收缩Log

时间:2019-12-07 04:06来源: 数据库
几日前风流罗曼蒂克台SQLServer 二零零六景逸SUV2的数据库在下午5点多抛出下边告急察与消防人员息: 行使Backup创造测验情况之后,开采testdb的LogFile过大,抵达400GB,由于测验情状实际上

几日前风流罗曼蒂克台SQL Server 二零零六景逸SUV2的数据库在下午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未被Log里德r读取。

   乍大器晚成看,还认为数据库损坏了(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.

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

注册成功未来,使用 sp_repldone,将装有的Transaction Log 标识为Distributed。

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

EXEC sys.sp_repldone 
        @xactid = NULL, 
        @xact_segno = NULL, 
        @numtrans = 0,     
        @time = 0, 
        @reset = 1  

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,

谈到底,使用DBCC ShrinkFile命令,Transaction Log File降低完结。

 

参考doc:

sp_repltrans (Transact-SQL).aspx)

sp_replicationdboption (Transact-SQL).aspx)

sp_repldone (Transact-SQL).aspx)

编辑: 数据库 本文来源:Shrink不能收缩Log

关键词:

  • 上一篇:没有了
  • 下一篇:没有了