How to read the transaction log for SQL Server

Brief introduction

This article describes what information is logged in the transaction log of SQL Server and how to read the information in these transaction logs. First, a Microsoft is not exposed to the function Fn_dblog, in the next part of the article mainly use this function to read the transaction log.

1. Fn_dblog (@StartingLSN, @EndingLSN)
2. [email protected]: Represents the starting LSN number, or null if the query starts from the first log record.
3. [email protected]: Indicates the end of the LSN number, or null value indicates the query to tail log records.
4. - It is important to note that the LSN we see here is hexadecimal, and the parameter here needs to be converted to decimal, as 00000021:00000077:0003 is converted to 33:119:3 when passed as a parameter to Fn_dblog .

Body

2. --Create a test database
4. Use [master];
6. GO
8. CREATE DATABASE TestDB;
10. GO
12. --Create a table
14. Use TestDB;
16. GO
18. CREATE TABLE [Location] (
20. [sr.no] intidentity,
22. [Date] Datetimedefault GETDATE (),
24. [City] CHAR (+) DEFAULT 'Xiamen');

A database named TestDB was created with the code above, and a table of three fields was created. Then take a look at the contents of the transaction log

2. Use TestDB;
4. GO
6. Select [Current LSN],
8. [Operation],
10. [Transaction Name],
12. [Transaction ID],
14. [Transaction SID],
16. [SPID],
18. [BeginTime]
20. from fn_dblog(null,null)

As you can see, a total of 195 rows of log records were generated, and I intercepted some of the results and recorded the actions of the corresponding LSN in the Operation column, where Lop_begin_xact represents the beginning of a transaction, Transaction name shows the name of the database being created, The Trasaction ID, however, records the corresponding transaction ID. Listed below are some of the more common and important values of operation

• lop_begin_xact the beginning of a transaction
• lop_lock_xact Get lock
• Lop_modify_row Modify Rows ( specifically modified objects can view allocunitname)
• lop_commit_xact Commit a transaction
• lop_delete_rows Delete Data
• lop_insert_rows Inserting Data

Next, insert the following into the table - data, and view the corresponding transaction log, the code is as follows:

2. INSERT into location defaultvalues
4. GO -
6. GO
10. SELECT
12. [Current LSN],
14. [Transaction ID],
16. [Operation],
18. [Transaction Name],
20. [CONTEXT],
22. [Allocunitname],
24. [Page ID],
26. [Slot ID],
28. [BeginTime],
30. [EndTime],
32. [Number of Locks],
34. [Lock Information]
36. from sys. Fn_dblog (null,null)
38. WHERE operation = 'lop_insert_rows' and allocunitname = 'dbo. Location '

Get the result as shown, the number of rows returned with US insert The same number of times, then take one of them Take a look at it once insert What actions are recorded in the transaction log.

1. SELECT
2. [Current LSN], [Transaction ID], [operation], [Transaction Name], [CONTEXT], [Allocunitname], [Page id], [Slot id], [ BeginTime], [EndTime], [number of Locks], [Lock information]
3. from sys. Fn_dblog (null,null)
4. WHERE [Transaction ID] = '0000:000002fc'

can see this transaction Details of the steps performed

• in 2014/05/25 18:35:39:197 transaction start
• < Span style= "font-family: the song Body;" > in heap table dbo. Location pageid 0001:0000004f insert data Span style= "Font-family:verdana;" >
• in Span style= "Font-family:verdana;" >2014/05/24 18:35:39:200 COMMIT transaction

The following paragraph is my Lock Information field to copy out the content, to see in detail

1. HoBt 72057594039042048:acquire_lock_ix object:6:245575913:0; Acquire_lock_ix page:6:1:79; Acquire_lock_x rid:6:1:79:0

let's verify by following the code that this one INSERT The lock information obtained by the statement

2. DBCC TRACEON ( -1,3604)
4. DBCC TRACEON ( -1,1200)--View the lock information for the current session
6. BEGIN TRAN
8. INSERT into location defaultvalues
10. ROLLBACK TRAN
12. DBCC Traceoff ( -1,1200)
14. DBCC Traceoff ( -1,3604)
16. /*
18. Process acquiring IX lock on object:6:245575913:0 (class bit2000000 ref1) Result:ok
20. Process acquiring IX lock on page:6:1:79 (class bit2000000 ref0) Result:ok
22. Process acquiring X lock on rid:6:1:79:90 (class bit2000000 ref0) Result:ok
24. */

can see Lock Information The information recorded in the field is - The information for the trace flag lock output is consistent.

also from the transaction log, you can see SQL Server some of the internal operations and see these operations for some specific information, such as the start time, the number of times, the steps of the operation, and so on. Next look at the page split action

2. --Find the transaction of the page split action
4. SELECT
6. [Current LSN], [Transaction ID], [operation], [Transaction Name], [CONTEXT], [Allocunitname], [Page id], [Slot id], [ BeginTime], [EndTime], [number of Locks], [Lock information]
8. from sys. Fn_dblog (null,null)
10. WHERE [Transaction Name] = 'SplitPage'
12. --view actions in specific transaction
14. SELECT
16. [Current LSN], [Transaction ID], [operation], [Transaction Name], [CONTEXT], [Allocunitname], [Page id], [Slot id], [ BeginTime], [EndTime], [number of Locks], [Lock information]
18. from sys. Fn_dblog (null,null)
20. WHERE [Transaction ID] = '0000:000002f8'

Conclusion

Knowing what is logged in the transaction log makes it easier for us to understand the execution of some of the operations that SQL Server does.