SQL Server to view database operation records using the system function fn_dblog

Use fn_dblog. The fn_dblog function is used to view database operation records.

SQL Server provides a large number of common functions. For example, the fn_dblog function can return the transaction logs of SQL Server to the user in the form of data table records. The function is used as follows.

: Fn_dblog (@ startinglsn, @ endinglsn)

The two parameters of this function represent the starting LSN and ending lsn respectively. By default, null values can be used. If @ startinglsn is set to null, the query starts from the first log record. If @ endinglsn is null, the query ends until the last log record is queried.

Prompt

LSN is the log sequence number, short for the log serial number. SQL server uses the transaction log to record the transactions that occur on the database server.

To view the log records related to the database northwind, follow these steps.

(1) In the query Editor, enter the following statements:

Use northwind
Select * From: fn_dblog (null, null)
Go

(2) Click execute. Each log information related to the database northwind is returned as a dataset.

(3) In addition, you can set query conditions like other common query statements, as shown below:

Use northwind
Select [begin time], * From: fn_dblog (null, null) Where [begin time]> = '2017/22'
Go

Tip:

You can use fn_dblog to monitor transaction operations such as data table update, insertion, and deletion. Therefore, after these operations are performed, the corresponding records appear in the database transaction log.