分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据-阿里云开发者社区

开发者社区> 杰克.陈> 正文

分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据

简介: 原文:分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据 分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据 今天开发找我,说数据库insert不进数据,叫我看一下 他发了一个截图给我 然后我登录上服务器,发现了可疑的地方,而且这个数据库之前有一段经历 在...
+关注继续查看
原文:分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据

分享工作中遇到的问题积累经验 事务日志太大导致insert不进数据

今天开发找我,说数据库insert不进数据,叫我看一下

他发了一个截图给我

然后我登录上服务器,发现了可疑的地方,而且这个数据库之前有一段经历

在月初的时候这个数据库曾经置疑过,启动不起来

Could not redo log record (163041:116859:5), for transaction ID (0:-1175226963), on page (17:20633999), database 'xxrchives' (database ID 7). Page: LSN = (162930:20671:38), type = 2. Log: OpCode = 2, context 3, PrevPageLSN: (163041:116230:18). Restore from a backup of the database, or repair the database.

During redoing of a logged operation in database 'xxxrchives', an error occurred at log record ID (163041:116859:5). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

An error occurred during recovery, preventing the database 'xxxrchives' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

Setting database option EMERGENCY to ON for database xxxchives.

The database 'xxxxchives' is marked EMERGENCY_MODE and is in a state that does not allow recovery to be run.

由于一些特殊的原因没有做备份,数据库大小差不多3TB,这里3TB是已经除去可用空间的了,里面只有几张表,其中只有一张大表

修复语句如下

USE MASTER
GO
ALTER DATABASE [xxxrchives] SET EMERGENCY
GO
ALTER DATABASE [xxxxchives] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB('xxxxchives','REPAIR_ALLOW_DATA_LOSS') with tablock
GO
ALTER DATABASE [xxxxchives] SET ONLINE
GO
ALTER DATABASE [xxxxchives] SET MULTI_USER
GO

数据相对来说不是非常重要,允许丢失一些数据

结果运行了差不多7天,业务也一度中断了

其实有数据库完整备份的话可以使用页面还原的方法还原有问题的页面,加上日志备份,而不用这么大工程的。。。

 

有经验的SQLSERVER管理员应该知道最后我使用的方法如何抛弃有824错误的页面,只保留正常的数据页面,这个大表是做了表分区的,由于篇幅关系这里不具体说了

消息 824,级别 24,状态 2,第 3 行
SQL Server 检测到基于一致性的逻辑 I/O 错误 pageid 不正确(应为 6:33780000,但实际为 0:0)。在文件 'E:\DataBase\FG_xxxxive\FG_xxxxhive_Id_04_data.ndf' 中、偏移量为 0x0000406e240000 的位置对数据库 ID 7 中的页 (6:33780000) 执行 读取 期间,发生了该错误。SQL Server 错误日志或系统事件日志中的其他消息可能提供了更详细信息。这是一个威胁数据库完整性的严重错误条件,必须立即纠正。请执行完整的数据库一致性检查(DBCC CHECKDB)。此错误可以由许多因素导致;有关详细信息,请参阅 SQL Server 联机丛书。

 


问题所在

这个数据库运行在SQL2008上面,一直都是简单模式,那么问题来了,由于当时checkdb的时间很长,积累了大量事务日志

但是,按道理处于简单模式会自动截断日志的,但是当时本人也没有看,心里只想着数据库马上online

 

步骤一:今天开发找我插不进去数据也是因为这个,我运行了下面语句,这个语句是我找问题的时候一般都先用这个语句,因为在徐海蔚老师的书里面也建议先使用这个语句

SELECT * FROM sys.[sysprocesses] 

结果发现

相当多的log等待

 

步骤二:我再运行下面脚本

-- =============================================
-- Author:      <桦仔>
-- Blog:        <http://www.cnblogs.com/lyhabc/>
-- Create date: <2014/4/18>
-- Description: <统计各个数据库的总大小V2 不包含数据文件>
-- =============================================
SET NOCOUNT ON 
USE master
GO

DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)



--临时表保存数据
CREATE TABLE #DataBaseServerData
(
  ID INT IDENTITY(1, 1) ,
  DBNAME NVARCHAR(MAX) ,
  Log_Total_MB DECIMAL(18, 1) NOT NULL ,
  Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL 
)



--游标
DECLARE @itemCur CURSOR
SET 
@itemCur = CURSOR FOR 
SELECT name from   SYS.[sysdatabases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB')


OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL=N'USE ['+@DBNAME+'];'+CHAR(10)
    +
    'INSERT  [#DataBaseServerData]
                ( [DBNAME] ,
                  [Log_Total_MB] ,
          [Log_FREE_SPACE_MB ] 
                )
                SELECT '''+@DBNAME+''', str(sum(convert(dec(17,2),sysfiles.size)) / 128,10,2) AS Total_MB,
                SUM(( database_files.size - FILEPROPERTY(database_files.name, ''SpaceUsed'') )) / 128.0 AS free_space_mb
                FROM    dbo.sysfiles as sysfiles INNER JOIN sys.database_files as database_files ON sysfiles.[fileid]=database_files.[file_id] WHERE sysfiles.[groupid]  =0
                AND database_files.[type] = 1;'
        EXEC (@SQL)
        FETCH NEXT FROM @itemCur INTO @DBNAME
    END 

CLOSE @itemCur
DEALLOCATE @itemCur

SELECT  *  FROM    [#DataBaseServerData]
DROP TABLE [#DataBaseServerData]

结果发现

上百G的日志文件

 

 

步骤三:我使用数据分析脚本也发现表里面的数据没有增加,按F5刷新了很多次

--数据分析

CREATE TABLE #tablespaceinfo
    (
      nameinfo VARCHAR(50) ,
      rowsinfo BIGINT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    )  
 
DECLARE @tablename VARCHAR(255);  
 
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + [name] + ']'
    FROM    sys.tables
    WHERE   type = 'U';  
 
OPEN Info_cursor  
FETCH NEXT FROM Info_cursor INTO @tablename  
 
WHILE @@FETCH_STATUS = 0
    BEGIN 
        INSERT  INTO #tablespaceinfo
                EXEC sp_spaceused @tablename  
        FETCH NEXT FROM Info_cursor  
    INTO @tablename  
    END 
 
CLOSE Info_cursor  
DEALLOCATE Info_cursor  
 
--创建临时表
CREATE TABLE [#tmptb]
    (
      TableName VARCHAR(50) ,
      DataInfo BIGINT ,
      RowsInfo BIGINT ,
      Spaceperrow  AS ( CASE RowsInfo
                         WHEN 0 THEN 0
                         ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))
                       END ) PERSISTED
    )

--插入数据到临时表
INSERT  INTO [#tmptb]
        ( [TableName] ,
          [DataInfo] ,
          [RowsInfo]
        )
        SELECT  [nameinfo] ,
                CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
                [rowsinfo]
        FROM    #tablespaceinfo
        ORDER BY CAST(REPLACE(reserved, 'KB', '') AS BIGINT) DESC  


--汇总记录
SELECT  [tbspinfo].* ,
        [tmptb].[Spaceperrow] AS '每行记录大概占用空间(KB)'
FROM    [#tablespaceinfo] AS tbspinfo ,
        [#tmptb] AS tmptb
WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS BIGINT) DESC  

DROP TABLE [#tablespaceinfo]
DROP TABLE [#tmptb]
View Code

 

 

步骤四下面检查 VLF

DBCC LOGINFO
View Code

有400多个VLF

 

 

步骤五:检查一下log为什么不能重用的原因

SELECT  DB_NAME([database_id]) AS dbname ,
        [log_reuse_wait] ,
        [log_reuse_wait_desc]
FROM    sys.[databases]

结果发现 数据库做了复制,我接手的时候这个数据库是不需要复制的,可能是以前的同事弄的

 

步骤六:我使用博客园里面i6first大牛的文章把复制干掉《你还可以再诡异点吗——SQL日志文件不断增长

EXEC sp_removedbreplication [xxxchives]

 

步骤七:然后再来收缩日志

USE [xxxxchives]
GO
DBCC SHRINKFILE (N'xxxxxchives_log' , 5000)
GO

 

弄完之后,数据库正常了,没有log等待,数据也在不断增加


总结

简单几个步骤:从发现问题到解决问题,有些人可能半天才能解决,有些人几分钟就可以解决,这就是积累经验的重要性

我自己的做法是多看书,多看博客园,在QQ群里看一下大家的问题,这就是经验了,快速解决问题的经验

 

本人也喜欢将工作中遇到的问题写在博客里面,以供大家参考,大家一起进步o(∩_∩)o 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
工作半年遇到最奇葩的问题
工作半年遇到最奇葩的问题 背景 公司最近买了一套项目,在启动的时候出现了一系列奇怪的问题,对方的技术栈要求是Tomcat7启动,但是由于我们公司出于安全的考虑所以是要求用Tomcat9进行启动的。 问题描述 下面情况都是相同war包相同Tomcat情况下 系统 Tomcat版本 能否启动 Windows Tomcat7 能 Windows Tomcat9 能 macOS Tomcat7 能 macOS Tomcat9 不能 Linux Tomcat7 能 Linux Tomcat9 不能 由于对于项目的不熟悉,导致找了很久才找出来原因。
779 0
sql server 完整备份、差异备份、事务日志备份
原文:sql server 完整备份、差异备份、事务日志备份 一、 理解:        完整备份为基础, 完整备份可以实物回滚还原,但是由于完整备份文件过大,对硬盘空间比较浪费这是就需要差异备份 或者 事务日志备份。
1155 0
Python系列直播——深入Python与日志服务,玩转大规模数据分析处理实战
Python系列直播——深入Python与日志服务,玩转大规模数据分析处理实战
4840 0
Android开发12——Andorid中操作数据库的insert的两种方法以及nullColumnHack
一、发现问题 先看两种方法插入数据 public void save(Person p) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.execSQL("insert into person(name,phone) values (?,?)", new Object[] { p.getName(), p.ge
850 0
MySQL之数据导入导出备份以及事务、存储引擎
MySQL之数据导入导出备份以及事务、存储引擎 数据导入 作用:把文件系统的内容导入到数据库中 操作步骤: 1.在数据库中创建对应的表 create table userinfo(  username varchar(20), .
1442 0
【大数据新手上路】“零基础”系列课程--Flume收集网站日志数据到MaxCompute
概述:大数据时代,谁掌握了足够的数据,谁就有可能掌握未来,而其中的数据采集就是将来的流动资产积累。 任何规模的企业,每时每刻都在产生大量的数据,但这些数据如何归集、提炼始终是一个困扰。而大数据技术的意义确实不在于掌握规模庞大的数据信息,而在于对这些数据进行智能处理,从中分析和挖掘出有价值的
6578 0
Kubernetes中Logtail日志采集CRD配置详解
日志采集配置默认支持控制台配置方式,同时针对Kubernetes微服务开发模式,我们还提供CRD的配置方式,您可以直接使用kubectl对配置进行管理。
5120 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载