How to find Current LSN?

Question

How to find Current LSN?

Zubeyir

SSCrazy

Points: 2559
More actions
July 28, 2006 at 9:42 am

#113832
Hi buddies!
Please help me, I'm stucked
I'm trying to make a trn restore over a db that is in the restoring state. (That is restored with norecovery)
You know if you attempt to restore a TRN file on a db while the TRN is earlier from the you get this error.
```
The log in this backup set terminates at LSN 231323000015712500001, which is too early to apply to the database.
A more recent log backup that includes LSN 239029000000366300001 can be restored.
```
What I need is to do is to get the current LSN number of the current database.
```
RESTORE HEADERONLY FROM DISK = 'D:\db.BAK'
```
gives me the first and last LSN numbers of the original back-up file but I the current LSN (indicated in the error above, latter LSN) of the db is higher then both of these values.
Sinse the db is in the middle of a restore
```
select * from ::fn_dblog(default, default)
```
clause does not work.
So I need to find the current LSN number of a db that is in "restoring" mode.
Thanks
Zubeyir

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

I am not sure if you can get the current LSN during a restore. First of all you cannot even connect to the db. I am not sure if you can connect to some systables to find out the status. If all you want to find ou is the status, you could use STATS=PERCENTAGE option in your restore command to get an idea of how far along you are in the process. Besides that I doubt if you can get any more information.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Zubeyir SSCrazy Points: 2559 More actions · Answer 2

Yes Dinakar, it is not possible to get the current LSN while db is in "restoring" mode. (at least I couldn't get it so far ) I think it is something that goes in the SQL Engine level. I'm going to share what I've found after a lab work.

The below are the LSN values of a full db backup (db_ prefixed values), a log file before this full backup (trn_early_ prefixed), a log file right after the full db backup (trn_current_ prefixed), and another log backup after the current one (trn_late_ prefixed) retrieved with command RESTORE HEADERONLY. db_needs value is the LSN number that is displayed when you try to restore an "earyl" or "late" file over the target database. (ie. A more recent log backup that includes LSN 23000000048200000 can be restored). Since the test db is a little one with no productional operations or updates on it, the LSN values very close to each other.

The LSN values are represented here in ascending order.

LSN Number        file_type

23000000047400000 db_DifferentialBaseLsn
23000000047400000 trn_early_CheckpointLsn
23000000047400000 trn_early_DifferentialBaseLsn
23000000048000000 db_CheckpointLSN
23000000048000000 db_FirstLSN
23000000048000000 trn_current_CheckpointLsn
23000000048000000 trn_current_DifferentialBaseLsn
23000000048000000 trn_current_FirstLsn
23000000048000000 trn_early_FirstLsn
23000000048000000 trn_early_LastLsn
23000000048000000 trn_late_CheckpointLsn
23000000048000000 trn_late_DifferentialBaseLsn
23000000048200000 db_LastLSN
23000000048200000 db_needs
23000000048500000 trn_current_LastLsn
23000000048500000 trn_late_FirstLsn
23000000048500000 trn_late_LastLsn

What we see here is LSN value db_needs is between trn_current_LastLsn and trn_current_FirstLsn values. But since we cannot get the LSN value db_needs programmatically, we cannot use this approach. Instead we can use db_LastLSN LSN value which is in the same range as db_needs LSN value. Both values can be retrieved by using RESTORE HEADERONLY command.

Therefore we can compare LastLSN values of the original back-up file and .TRN files in order to find the first log back-up file which is appropriate to restore the original database. We can iterate through the .TRN files from earlier to later and make a logical comparison like this;

LastLSN value of the db back-up file < LastLSN value of TRN file

If this equation evaluates to true then you can use current .TRN file as the first .TRN file to restore over the database.

This is the solution that I found and works for two days so far. If any problems arise I'll post back to here.

Regards,

Zubeyir.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

Hi,

if you do the restore with standby instead of with norecovery you can get the current LSN:

select top 0 * into #dblog1 from ::fn_dblog( default, default )

insert #dblog1 exec (' use sitesql select top 1000 * from ::fn_dblog( default, default )')

select @hexlsn = (select min([Current LSN]) from #dblog1)

select @lsn = master.dbo.lsn2dec(@hexlsn)

SELECT @msg = convert(char(25),getdate(),113)+': Current LSN of the DB is ' + convert(nvarchar,@lsn) + '.'

RAISERROR(@msg,10,1) WITH NOWAIT

CREATE FUNCTION Lsn2Dec(@hexlsn nvarchar(22))

RETURNS decimal(38,0) AS

BEGIN

declare @lsn decimal(38,0)

-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')

select @lsn = master.dbo.HexToInt(convert(nchar,left(@hexlsn,patindex('%:%',@hexlsn)-1)))

select @lsn = @lsn * 10000000000

select @lsn = @lsn + master.dbo.HexToInt(substring(@hexlsn,

patindex('%:%',@hexlsn)+1,

charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)-patindex('%:%',@hexlsn)-1))

select @lsn = @lsn * 100000 + master.dbo.HexToInt(substring(@hexlsn,charindex(':', @hexlsn,patindex('%:%',@hexlsn)+1)+1,len(@hexlsn)))

return @lsn

END

regards

karl

Best regards
karl