SQL Server log (iv)

In the full recovery model, SQL Server records all the logs in the log file, so how does SQL Server log the logs?

Check the contents of the log file

   Select* from Sys.fn_dblog (null,null)

CURENTLSN: Current log sequence number

Operation: Type of operation

TransactionID: Transaction ID

Log record fixed length: Record the amount of virtual log files

PREVIOUSLSN: Previous log sequence number

Allocunitid: Modified data belongs to allocation unit ID

Allocunitname: Modified Object Name

Page ID: The pages where the data resides

Slot ID: The first row of the page on which the data is recorded

CheckPoint Begin:checkpoint Start time

CheckPoint End:checkpoint End Time

Dirty Pages: Dirty data page

Oldest replicated Begin LSN: If the database configuration is replicated, the oldest replication LSN

Next replicated End LSN: Next Replication LSN

Last distributed End LSN: the latest distribution LSN

Beginlog Status: Start logging the state of the transaction log, which indicates that the transaction log is now logged properly

Begin Time: Transaction start

Transaction Time: Transaction start

End Time: Transaction end

Transaction begin: Records the cureent LSN of the begin Transaction of this transaction

Preplog Begin LSN: The previous transaction log LSN before starting the database

Prepare time: Ready to start the database

New Split page: Which data page generated the page split

Rows Deleted: How many rows of data pages have been deleted

Description: What does this business do?

-----------------------------------------------

Common operation and context

< Strong>operation	Context	explanation
LO P_set_bits	LCX_ Diff_map	set bitmap, Data: Differential (differential) Backup: Only after the last full backup is backed up, make the modified portion. The backup unit is a zone (Extent). means that even if only one page has been changed in a zone, it will be reflected in the differential backup. Differential backups depend on a bitmap for maintenance, a bit corresponds to a zone, and since the last full backup, the modified area is set to 1, while the corresponding zone in bitmap is backed up by a differential backup. After the next full backup, all the bits in bitmap will be reset to 0 DCM page Variance Change (differential Changed MAP,DCM) page He tracks which area of a file has been modified since the latest full database backup. SQL Server uses incremental backups only for partitions that have data changes that have occurred
Lop_begin_xact		Transaction start
Lop_modify_row	Lcx_heap	Modify a row of records in a heap table
Lop_prep_xact		Prepare to start the database
Lop_commit_xact		Commit a transaction
Lop_modify_row	Lcx_boot_page	Modify the Database startup page
Lop_modify_header	Lcx_pfs	Modify page header information for a PFS page
Lop_insert_rows	Lcx_clustered	Insert data to index page of clustered index
Lop_insert_rows	Lcx_index_leaf	The leaf node that inserts data into the index is the data page
Lop_format_page	Lcx_clustered	Reorganize clustered Indexes
Lop_delete_split	Lcx_clustered	Deleting a row of records from a clustered Index table causes page splits
Lop_modify_header	Lcx_heap	To modify the header information for a page of a heap table
Lop_begin_ckpt	Lcx_null	Check Point start
Lop_end_ckpt	Lcx_null	Check Point end
Lop_set_free_space	Lcx_pfs	Modify the PFS page settings that data page is free
Lop_root_change	Lcx_clustered	Root node change of clustered index
Lop_insert_rows	Lcx_heap	Inserting data into the heap table

--------------------------------------------------------------------------------------------------------------- -------------

In bulk-logged mode, the log does not record operations on data pages, and when using bulk-logged operations such as Bcp,bulk Insert,select into, the changes to the data and the records that are inserted in the data are not found in the log records.

Therefore, in the bulk-logged mode, the insertion speed is relatively fast.

SQL Server log (iv)