分享工作中遇到的問題積累經驗 事務日志太大導緻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 有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