How do I identify last transaction log applied on a standby?

Question

Would like to do this using tsql please.
thank you.

Answer 1

If I understand your question you want to find out last transaction log applied

 

 

You can query restorehistory, restorefile tables to get that information

 

The following will show you last  transaction log applied to the database

/* change YourDBName in the script here*/ 

SELECT Top 1

destination_database_name as 'Database Name',

[user_name] as 'Username',

--CASE restore_type

--WHEN NULL THEN 'NULL'

--WHEN 'D' THEN 'Database'

--WHEN 'F' THEN 'File'

--WHEN 'G' THEN 'Filegroup'

--WHEN 'I' THEN 'Differential'

--WHEN 'L' THEN 'Log File'

--WHEN 'V' THEN 'Verifyonly'

--WHEN 'R' THEN 'Revert'

--END as 'Restore Type',

backup_set_id,

Case [replace]

WHEN NULL THEN 'NULL'

WHEN 1 THEN 'YES'

WHEN 0 THEN 'NO'

END as 'Database Replaced',

restore_date as 'Date Restored'

FROM msdb..restorehistory

where destination_database_name = 'YourDBName'

and restore_type = 'L'

order by restore_date desc

 

Answer 2

This is good thanks.

I see from this method that I can identify it by backup_set_id. I then queried the backupset table. This way i was able to identify the txn log by comparing start/end backup time to the actually trn file name.
Having said that, I did not see anywhere the actual txn log backup file name to confirm for sure. Is the actuall file name e.g. 'test_db_20091015110016.trn' tracked anywhere in restore metadata?

thank you.

Answer 3

You can use physical_device_name from  backupmediafamily

 

/* Change yourDBName  */

 

SELECT TOP 1

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = 'D' THEN 'Database'

WHEN rsh.restore_type = 'F' THEN 'File'

WHEN rsh.restore_type = 'G' THEN 'Filegroup'

WHEN rsh.restore_type = 'I' THEN 'Differential'

WHEN rsh.restore_type = 'L' THEN 'Log'

WHEN rsh.restore_type = 'V' THEN 'Verifyonly'

WHEN rsh.restore_type = 'R' THEN 'Revert'

ELSE rsh.restore_type

END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

where destination_database_name = 'YourDBName'

and restore_type = 'L'

ORDER BY rsh.restore_date DESC