How to Find Current LSN Number in SQL Server Database

You can get the current LSN in SQL database to examine how much space is available for growth in log sequence number.

What is LSN in SQL Server?

In partitioned database environments, you must look at LSNs on each partition, as LSNs in different database partitions grow independently of one another.

How to Find the Current Log Sequence Number (LSN)

You can use db2pd command to determine the current LSN for a given SQL Server database.

For more information about how to use the db2pd command to determine the LSN, read the following instructions:

1. Run the db2pd command with -logs option. For example:

db2pd - logs – TEST_DB

2. Look at the output from the preceding command to determine the current LSN.

Note: If you are using the database version of 9.5 Fix Pack 4 or its earlier version then you need to perform the given steps to find the current LSNs.

1. Determine the current log.
2. Look at the next log file in sequence to see which is the starting LSN in the particular log file. For the purposes of determining how much LSN headroom you have, assume that this LSN is the current LSN. It is not actually the current LSN, but it is a conservative estimate.

From this report you can get the current log sequence number.

How to Monitor the LSNs Growth Rate in SQL Server Databases?

If you want to avoid the need to monitor the growth of Log Sequence Numbers (LSNs), upgrade to DB2 Version 9.7. This version of the product has higher limits for LSNs, effectively eliminating the need to monitor LSN growth.

If you want to upgrade from DB2 Version 9.7 then this is not a good option, at this situation you must monitor the growth of LSNs. The monitoring of LSNs helps to take any required actions before they grow too large.

Steps given below requires time to time, if you want to monitor the Log Sequence Number Growth:

1. Examine the Current LSN (Log Sequence Number).
2. Calculate the assigning rate of the LSNs with the help of current LSN and the LSN start from the origin.
3. Use the LSN growth rate to predict how long it will take to reach the maximum-allowed LSN before the database becomes read-only.

Conclusion

Here I have discussed about how to find current LSN number in SQL Server database and what are the importance of LSNs. Here I have tried to explain that how can we monitor the log growth rate in SQL Database.