SQL Server transaction Log analysis

SQL Server transaction Log analysis

Introduction to the Fn_dblog () and Fn_dump_dblog () functions

SQL Server has two exposed functions fn_dblog () and Fn_dump_dblog () are useful and provide a large amount of information. You can use these functions to get a lot of useful information from more than 100 columns.

Fn_dblog () is used to parse the database's current transaction log file, which requires two parameters, the transaction start LSN and end LSN, respectively, by default, NULL, which indicates that all log records for the transaction log file are returned.

For example:

SELECT * from Fn_dblog (null,null);

Fn_dump_dblog () is used to analyze the transaction log backup file for the database, which requires a lot of parameters, but we only need the full path name of the incoming backup file, and the other parameters use 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,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); 

Let's look at the performance of multiple transaction operations written to the transaction log file:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9D/B1/wKiom1mEE2uhA1vQAAEAu2NNtMM051.png "style=" float : none; "title=" Transaction log file operation "alt=" Wkiom1mee2uha1vqaaeau2nntmm051.png "/>

Important Data output column values

Let's analyze several important columns in the 100 multi-column output:

[Transaction Name]

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

INSERT, UPDATE, DELETE, Dropobj

The secondary values are:

Allocpages, SplitPage, Allocheappagesysxactdml, Updateqpstats, Backup:commitlogarchivepoint, BTree Split/shrink and so on.

A typical application is to find an object delete operation by dropobj the value.

[Operation]

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

Lop_begin_xact, Lop_commit_xact, Lop_insert_rows, Lop_delete_rows, Lop_modify_row, Lop_modify_columns

The 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 can be associated with the query to which table or index is affected.

[TRANSACTION SID]

The user SID of the transaction operation, which can be converted to a user name by using the SUSER_SNAME () function.

Specific example Analysis

Let's 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,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 ';

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9D/B1/wKiom1mEE2yQ1NNRAAA5E0BvQFg354.png "title=" 1.png "Style=" Float:none; "alt=" Wkiom1mee2yq1nnraaa5e0bvqfg354.png "/>

According to [Transaction Name] For insert know this is an insert operation, which is the inserted data row, which is the index row, can be based on the following PartitionID again to associate the query to.

Depending on the [TRANSACTION SID] You can query to the user of the operation:

SELECT SUSER_SNAME (0X017017A631B52141B2338990DCFFADCC);

object to action based on [PartitionID] query:

SELECT so.namefrom sys.objects soinner JOIN sys.partitions sp on so.object_id = Sp.object_idwhere partition_id in (72057594 041204736,72057594070630400);

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9D/B1/wKioL1mEFYLSLTjGAAAUxr_EIN8234.png "title=" 4.png "alt=" Wkiol1mefylsltjgaaauxr_ein8234.png "/>

Depending on the partition_id, you can also see in more detail whether the rows are data or indexed:

--View specific data distribution for 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 '); 

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9D/B1/wKioL1mEE2zyWzJkAAAoX96zCks728.png "title=" 2.png "Style=" Float:none; "alt=" Wkiol1mee2zywzjkaaaox96zcks728.png "/>

--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 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 '  ENDFROM  sys.indexes idxinner join sys.index_columns idxcon&nbsP;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.name= ' Notificationrecord ';

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9D/B1/wKioL1mEE23AWvh0AAAdWg4puPY384.png "title=" 3.png "Style=" Float:none; "alt=" Wkiol1mee23awvh0aaadwg4pupy384.png "/>

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1953572

SQL Server transaction Log analysis