分享工作中遇到的问题积累经验 事务日志太大导致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 MASTERGOALTER DATABASE [xxxrchives] SET EMERGENCYGOALTER DATABASE [xxxxchives] SET SINGLE_USER WITH ROLLBACK IMMEDIATEGODBCC CHECKDB('xxxxchives','REPAIR_ALLOW_DATA_LOSS') with tablockGOALTER DATABASE [xxxxchives] SET ONLINEGOALTER DATABASE [xxxxchives] SET MULTI_USERGO

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

结果运行了差不多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 masterGODECLARE @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 CURSORSET @itemCur = CURSOR FOR SELECT name from   SYS.[sysdatabases] WHERE [name] NOT IN ('MASTER','MODEL','TEMPDB','MSDB','ReportServer','ReportServerTempDB')OPEN @itemCurFETCH NEXT FROM @itemCur INTO @DBNAMEWHILE @@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 @itemCurDEALLOCATE @itemCurSELECT  *  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 CURSORFOR    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 tmptbWHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS BIGINT) DESC  DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]

步骤四：下面检查 VLF

DBCC LOGINFO

有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]GODBCC SHRINKFILE (N'xxxxxchives_log' , 5000)GO

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

总结

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

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

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

如有不对的地