Shulou(Shulou.com)06/01 Report--

SQL Server transaction log analysis

Introduction to fn_dblog () and fn_dump_dblog () functions

SQL Server has two unexposed functions, fn_dblog () and fn_dump_dblog (), which are very useful and provide a lot of information. You can use these functions to get a lot of useful information from more than 100 columns.

Fn_dblog () is used to analyze the current transaction log file in the database. It requires two parameters, the transaction start LSN and the end LSN. The default is NULL, which returns all log records of the transaction log file.

For example:

SELECT * FROM fn_dblog (null,null)

Fn_dump_dblog () is used to analyze the transaction log backup file of the database. This function requires a lot of parameters, but we only need to pass in the full path name of the backup file, and the other parameters use the default value of DEFAULT.

For example:

SELECT * FROM fn_dump_dblog (NULL, NULL, 'DISK', 1,' D:\ Pay\ Pay_201707280400_LOG.trn',DEFAULT, DEFAULT,DEFAULT, DEFAULT DEFAULT,DEFAULT, DEFAULT)

Let's take a look at the following figure of how multiple transaction operations are written to the transaction log file:

Important data output column values

Let's take a look at several important columns out of more than 100 columns of output:

[Transaction Name]

This column describes the type of transaction operation, and the main values are:

INSERT 、 UPDATE 、 DELETE 、 DROPOBJ

Secondary values are:

AllocPages, SplitPage, AllocHeapPageSysXactDML, UpdateQPStats, Backup:CommitLogArchivePoint, BTree Split/Shrink, etc.

A typical application is to find object deletion operations through the DROPOBJ value.

[Operation]

This column describes the specific types of operations recorded in the log, and the main values are:

LOP_BEGIN_XACT 、 LOP_COMMIT_XACT 、 LOP_INSERT_ROWS 、 LOP_DELETE_ROWS 、 LOP_MODIFY_ROW 、 LOP_MODIFY_COLUMNS

Secondary values are:

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]

The start time of the transaction operation.

[PartitionID]

Which partition of the specific operation, you can associate the query to which table or index is affected.

[TRANSACTION SID]

The user SID of the transaction operation can be converted to a user name through the SUSER_SNAME () function.

Analysis of specific examples

Let's take a look at a specific transaction operation:

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) WHERE [Transaction ID] = '0000Vera 5c9b41e2'

According to [Transaction Name] for INSERT, we know that this is an insert operation, which is the inserted data row and which is the index row, which can be queried according to the following PartitionID.

Users who can query the operation according to [TRANSACTION SID]:

SELECT SUSER_SNAME (0x017017A631B52141B2338990DCFFADCC)

Query the object of the operation according to [PartitionID]:

SELECT so.nameFROM sys.objects soINNER JOIN sys.partitions sp on so.object_id = sp.object_idWHERE partition_id in (72057594041204736, 72057594070630400)

Depending on partition_id, you can also see whether it is a data row or an index row in more detail:

View the specific data distribution of a table 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_pageFROM sys.objects soINNER JOIN sys.partitions sp ON so.object_id = sp.object_idINNER JOIN sys.indexes si ON sp.object_id = si.OBJECT_ID AND sp.index_id = si.index_idINNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_idINNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_idWHERE so.object_id = object_id ('NotificationRecord')

-- View the index details of a table SELECTTableId= 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 Nissan' END, [UQIQUE] = CASE WHEN IDX.is_unique=1 THEN N' √ 'ELSE Native' END,Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N' √ 'ELSE nasty' END,Disabled=CASE WHEN IDX.is_disabled=1 THEN N' √ 'ELSE nasty' END,Fill_factor=IDX.fill_factor Padded=CASE WHEN IDX.is_padded=1 THEN N' √ 'ELSE Noble' ENDFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX. [object _ id] = IDXC. [object _ id] AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX. [object _ id] = KC [parent _ object_id] AND IDX.index_id=KC.unique_index_idINNER JOIN sys.objects OON O. [object _ id] = IDX. [object _ id] INNER JOIN sys.columns CON O. [object _ id] = C .[ object _ id] AND O.type='U'AND O.is_ms_shipped=0AND IDXC.Column_id=C.Column_id where O.nameplate recording NotificationRecord'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.