Daniel Calbimonte
Exploring a SQL log with a SQL Server logs reading tool

Go LSN in SQL Server

November 25, 2021 by

Introduction

If you are reading this article is because you read about the SQL Server transaction log and you want to go to the LSN to restore information. This article will explain what is the LSN and show some tables, views, and system functions used to retrieve LSN information. We will also learn how to restore information using the Transaction Log information. We will also find out if there is a Go LSN command. So, if you want to learn more about the transaction log and want to recover some information using the logs, you are in the right place.

What is the LSN?

Let’s talk about LSN first. LSN stands for Log Sequence Number. The transaction log stores the database activity (the transactions). For example, if we insert, delete or update data we will have a transaction registered in the Transaction log.

The Transaction log is a physical file which by default has the ldf extension. By viewing the properties of the database, you can check the location of your transaction log file in SSMS:

Physical location of the transaction log files

If you prefer to use T-SQL, the following sentences will be useful to get the path of the log file of the current database:

How can I do a go LSN?

It is a common question if we can go to a specific LSN. Another frequent question is if there is a go LSN command.

Well, the answer is no, there is no go LSN command. However, there are functions to go to check the LSN. Let’s look at the Transaction Log File first. To do it, we will use an undocumented function named the fn_dblog. As the name says, it is a function to read the database transaction log.

Let’s look at the query:

The results displayed will show the Current LSN, Operation (insert, update, commit, checkpoint, begin time, end time, etc.), transaction ID and several different columns:

sys.fn_dblog information

It is very important to know the LSN number when you are restoring data. The backup set is a system table that is stored in the system MSDB database and is used to get the information about the backup sets including information about the backup, password protection, recovery information, collation, the server used, compression and encryption information and more.

dbo.backupset information related to collation, password protetion, snapshot info.

It will also include important information related to the first LSN and last LSN. The first LSN contains the oldest LSN of the backup log record. The last_LSN will show the next LSN after the backup.

dbo.backupset information related to the last LSN and first LSN

  • Note: For more detailed information about the dbo.backupset, please refer to the following reference: backupset (Transact-SQL)

As you can see, there is no go LSN, but we have some functions to view it.

Restore data using a specific LSN

As we said before, there is no way to do a go LSN, but we can restore data using the LSN. The following example will show how to restore data in a specific LSN:

The following command will restore the transaction log backup from a specified LSN. The WITH STOPATMARK allows writing the transaction log LSN.

There must be another way

If you want to restore your data, but you want to check an easier way to recover information using the log, but you think it is hard to read the log using system tables or system non-documented functions, there is another way to do it. In fact, you do not need to know anything about LSN to recover your data using the transaction log.

I found on the web a nice tool to read the log, recover data, export information Undo and Redo changes. You can detect who did the transaction and recover data using the log information.

Exploring a SQL log with a SQL Server logs reading tool

The tool name is the ApexSQL Transaction Log Reading. For more information about this tool, please check the following link: Transaction log

Go to the LSN for the master database and other data files

The master database file is one of the main system databases containing important information about SQL Server including logons, linked server, server configuration information, system stored procedures, and more. We can use the sys.master_files to get the LSN of the database creation. Also, we can get the LSN of the differential backup. Backup_lsn shows the LSN of the last backup.

A similar system view, is the sys.database_files that shows similar information about the data files.

database_files system view

For more information about the sys.master_files and the sys.database_files, refer to the following links:

Conclusion

In this article, we learned what the transaction log is and how it can be used to recover information using the transaction information. We learned how to go to a specified LSN. We also show different ways to read the transaction log and different ways to recover information from a selected LSN. We also, show some system views and functions used to retrieve LSN information in the database.

If you have questions, feel free to write your comments and we will gladly help you.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
Transaction log

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

114 Views