SQL Server事務日志分析
目前成都創(chuàng)新互聯(lián)公司已為上1000家的企業(yè)提供了網(wǎng)站建設、域名、網(wǎng)頁空間、網(wǎng)站托管維護、企業(yè)網(wǎng)站設計、東鄉(xiāng)族網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。
fn_dblog()和fn_dump_dblog()函數(shù)介紹
SQL Server有兩個未公開的函數(shù)fn_dblog()和fn_dump_dblog()非常有用并且提供的信息量很大。你可以使用這些函數(shù)來獲取100多列大量的有用信息。
fn_dblog()用于分析數(shù)據(jù)庫當前的事務日志文件，它需要兩個參數(shù)，分別為事務開始LSN和結(jié)束LSN，默認為NULL，表示返回事務日志文件的所有日志記錄。
例如：
SELECT * FROM fn_dblog(null,null);
fn_dump_dblog()用于分析數(shù)據(jù)庫的事務日志備份文件，該函數(shù)需要的參數(shù)很多，但我們只需要傳入備份文件的完整路徑名稱，其他參數(shù)使用默認值DEFAULT。
例如：
SELECT * FROM fn_dump_dblog ( NULL, NULL, 'DISK', 1, 'D:\Pay\Pay_201707280400_LOG.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
再來看看下圖多個事務操作寫入到事務日志文件的表現(xiàn)：
重要數(shù)據(jù)輸出列值
我們再來分析下100多列輸出中的幾個重要列：
[Transaction Name]
該列描述該事務操作的類型，主要值有：
INSERT、UPDATE、DELETE、DROPOBJ
次要值有：
AllocPages、SplitPage、AllocHeapPageSysXactDML、UpdateQPStats、Backup:CommitLogArchivePoint、BTree Split/Shrink等。
典型的應用是通過DROPOBJ值來查找對象刪除操作。
[Operation]
該列描述日志里記錄的操作的具體類型，主要值有：
LOP_BEGIN_XACT、LOP_COMMIT_XACT、LOP_INSERT_ROWS、LOP_DELETE_ROWS、LOP_MODIFY_ROW、LOP_MODIFY_COLUMNS
次要值有：
LOP_BEGIN_CKPT、LOP_END_CKPT、LOP_XACT_CKPT、LOP_LOCK_XACT、
LOP_DELETE_SPLIT、LOP_EXPUNGE_ROWS、LOP_MODIFY_HEADER、LOP_FORMAT_PAGE、LOP_COUNT_DELTA、LOP_HOBT_DELTA、LOP_INSYSXACT、LOP_INVALIDATE_CACHE、LOP_MIGRATE_LOCKS、LOP_SET_BITS、LOP_SET_FREE_SPACE、LOP_SHRINK_NOOP、LOP_TEXT_INFO_BEGIN、LOP_TEXT_INFO_END
[Begin Time]
事務操作的開始時間。
[PartitionID]
具體操作的哪個分區(qū)，可以關聯(lián)查詢到具體影響的哪個表或索引。
[TRANSACTION SID]
該事務操作的用戶SID，可以通過SUSER_SNAME()函數(shù)轉(zhuǎn)換為用戶名。
具體示例分析
再來看一個具體事務操作：
SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID] FROM fn_dump_dblog ( NULL, NULL, 'DISK', 1, 'D:\Pay\Pay_201707280400_LOG.trn', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) WHERE [Transaction ID]='0000:5c9b41e2';
根據(jù)[Transaction Name]為INSERT知道這是一個插入操作，具體哪條是插入的數(shù)據(jù)行，哪條是索引行，可以根據(jù)后面的PartitionID再去關聯(lián)查詢到。
根據(jù)[TRANSACTION SID]可以查詢到操作的用戶：
SELECT SUSER_SNAME(0x017017A631B52141B2338990DCFFADCC);
根據(jù)[PartitionID]查詢到操作的對象：
SELECT so.name FROM sys.objects so INNER JOIN sys.partitions sp on so.object_id = sp.object_id WHERE partition_id in( 72057594041204736, 72057594070630400);
根據(jù)partition_id還可以更詳細的查看是數(shù)據(jù)行還是索引行：
重慶分公司
重慶分公司
--查看某個表的具體數(shù)據(jù)分布
SELECT DISTINCT so.name AS 'table_name', so.object_id,sp.partition_id,si.name AS 'index_name',internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page, first_page, root_page
FROM sys.objects so
INNER JOIN sys.partitions sp ON so.object_id = sp.object_id
INNER JOIN sys.indexes si ON sp.object_id = si.OBJECT_ID AND sp.index_id = si.index_id
INNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_id
INNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_id
WHERE so.object_id = object_id('NotificationRecord');
--查看某個表的索引詳細信息 SELECT TableId=O.[object_id], TableName=O.Name, IndexId=ISNULL(KC.[object_id],IDX.index_id), IndexName=IDX.Name, IndexType=ISNULL(KC.type_desc,'Index'), Index_Column_id=IDXC.index_column_id, ColumnID=C.Column_id, ColumnName=C.Name, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END, [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END, Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END, Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END, Fill_factor=IDX.fill_factor, Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN sys.objects O ON O.[object_id]=IDX.[object_id] INNER JOIN sys.columns C ON O.[object_id]=C.[object_id] AND O.type='U' AND O.is_ms_shipped=0 AND IDXC.Column_id=C.Column_id where O.name='NotificationRecord';
