SQL Server log file structure and mis-operation data retrieval

Original: SQL Server log file structure and error operation data retrieval

I. Overview

In SQL Server, there are data files. mdf and log files. ldf, the log file is another important part of the SQL Server database, and the log file records all transactions and the changes made to the database by each transaction. In order to improve the performance of the database, SQL Server data is cached in memory and is not written to disk in real time until the database checkpoint (checkpoint) or insufficient memory must (Lazy write) write changes to the data to disk. SQL Server generates log records when transactions are turned on and modifications are made to the in-memory data. SQL Server Insert modification Delete for data pages commits the transaction after it is completed in memory, but does not synchronize to the data page of the hard disk. In order to ensure the consistency of database transactions such as (server crashes, power outages) and other in-memory modifications are not written to the hard disk, the next time you restart to be able to revert to a transactionally consistent point in time, you must rely on the transaction log.

1.1 Storage structure

Unlike data files, log files are not organized by page/area. Each physical log file is divided into multiple virtual log units, the virtual log unit is not fixed size, and the number is not fixed, and the administrator cannot configure the size and quantity. For example, each time the log file grows automatically (by default, 10% of the space is expanded), at least one virtual unit is added.

The transaction log is a wrapped file. For example, a log file in a database consists of 5 virtual log units, and when the database is created, the logical log files start at the beginning of the physical file, the new log records are added to the logical log end, and then the physical log is expanded to the end.

When the end of the logical log reaches the end of the physical log, the new logging wraps around to the beginning of the physical log file and continues to write back (this is because the log backups are truncated to make the log space reusable).

is the log file flowchart, after log backup virtual logs 1 and virtual log 2 will be truncated, virtual log 30% for the beginning of the logical log, when the virtual log 3 and virtual log 4 after the use of the backup again, because the log file is a wrapped file, at this time from the virtual log 1 starts.
Figure 1st the appearance of the log file

　　

Figure 2 Circular use of the transaction log

In a virtual log unit, divided into blocks, there are specific log records in the block, each log record has an LSN (log Sequence number) numbered, this number consists of three parts. The first part is the virtual log unit (Vsan log File) sequence number, the second part is the number of blocks in the virtual log unit, and the third part is the number of the log records in the block. For an LSN, its number is 000001d:000000fd:0002. This indicates that the LSN belongs to virtual log 000001D, which belongs to block 000000FD and corresponds to record 2 in that block.

1.2 DBCC LOG
Using DBCC log to see what information is stored in the log file, DBCC log (dbname, formart_id), formart_id using the "3" parameter output will be more detailed.

Create DatabaseTestlogGo UseTestlogGoCreate TableTest (IDint, namenvarchar( -))GOInsert  intoTestValues(1,'AAAA')UpdateTestSetName='bbbb' whereId=1GoDBCCTraceon (3604)GoDBCC Log(Testlog,3)

Because DBCC LOG is an unlisted command, no description is found, including information about the current ordinal number, operation type, transaction number, and so on.

Two. ApexSQL Log tool

Because the DBCC log data is not very intuitive, now through the third-party tool ApexSQL log to view, the tool can see the above table creation, INSERT, UPDATE, delete the operation record, in the database log file also marked the starting schedule, and the operation by which user, for each operation, You can see more specific update information.

This is the two records that have just been manipulated as shown in

Check Insert to find the statement do undo (undo rollback old value overrides) and redo (submit new value overrides)

　　

--Undo INSERT (0000001e:00000047:0013) done at 2018-07-29 09:49:55.570 by HSR-PC\HSR in transaction 0000:00000301 (Co mmitted)BEGIN TRANSACTIONDELETE  from [dbo].[Test] WHERE /** * Warning:where CLAUSE for this STATEMENT is GENERATED for A TABLE with no PRIMARY KEY and no CLUSTERED INDEX ***/[ID] = 1  and [name] =N'AAAA'COLLATE chinese_prc_ci_asIF @ @ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION;PRINT 'error:statement affected more THAN one ROW. All the changes were rolled back.' END

 -- redo INSERT (0000001e:00000047:0013) Done At 2018-07-29 09:49:55.570 by HSR-PC\HSR in transaction 0000:00000301 (Committed)   INSERT  into  [ dbo  ] .  [ test  ]  ([ id  ]  , [ name  ]  ) values  (1 , N " aaaa  "  COLLATE chinese_prc_ci_as) 

--the following id=1 statements do four thingsUpdateTestSetName='CCCC' whereId=1UpdateTestSetName='dddd' whereId=1UpdateTestSetName='eeee' whereId=1Delete  fromTestwhereId=1

The following records the appropriate actions, trial restricted may be due to the cost of the software.

Summary: Using TRUNCATE TABLE to delete an operation is not logged, and the undo operation cannot be done. Logging is related to the amount of data that is actually modified, and the changes to each record save the log records. The SQL Server log can read the values before the data is modified and the modified values.

Reference documents:

Sq lserver2012 implementation and management Practical guide

SQL Server log file structure and mis-operation data retrieval