View SQL server log Part 2

In Part1, we mentioned that you can use fn_dblog to view active logs. However, if the logs are backed up, can we still view them?

The answer is yes. Use fn_dump_dblog.

 

Below is my own test:

1. -- insert data to table

Insert into prodtable2defavaluvalues;

Go 1, 1000

 

2. --- query log information

Select *
From fn_dblog (null, null) whereoperation = 'lop _ insert_rows'

Check 100 records.

3. -- backup log

Backup log fndblogtesttodisk = 'd: \ MSSQL \ fndblogtest. trn'

 

4. --- query log information

Select *
From fn_dblog (null, null) whereoperation = 'lop _ insert_rows'

(0 row (s) affected)

We can see that the log has been backed up and no active log exists. Therefore, fn_dblog does not return any information.

We can use the following statement to check:

5.-query from the backup log:

Select *
Fromfn_dump_dblog (

Null, null, 'disk', 1, 'd: \ MSSQL \ fndblogtest. 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)

Whereoperation = 'lop _ insert_rows'

Go

The query result shows 1000 records:

The query result is the same as that of fn_dblog before log backup.

 

Note: 63 default null parameters must be specified. Otherwise, data cannot be returned. The meanings of the first five parameters are as follows:

 

1. Start lsn

2. End lsn

3. Backup file type (disk or type)

4. Backup File sequence number (assuming multiple backups are to the same file)

5. Backup File Name

 

For more details, refer to Paul's article on sqlskill (Using fn_dblog, fn_dump_dblog, and restoring
With stopbeforemark to an lsn)