SQL Server Performance

best way to see last LSN applied to the secondary

Discussion in 'SQL Server Log Shipping' started by BikeBoy, Oct 14, 2005.

  1. BikeBoy New Member

    Hi all,

    Let's say logshipping breaks, and you get an error:
    "RESTORE FAILED, ....APPLY LATER LSN log to the secondary database."

    Is there an easy way (other than to dig through sql logs) to find out which log file was last applied to the database? That way I could delete all previous logs, and rerun the restore job, whithout reinstalling logshipping from scratch.

    Thank you
  2. satya Moderator

    If you are using SQL 2000 log shipping wizard then use monitor server logs to see the last restored log file. If it fails then the best bet is to stop log shipping and restore full database onto secondary server then continue the log shipping.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. BikeBoy New Member

    Satya,

    If you mean looking at the logshipping monitor, I see the proper last backup file (don't care about that), but for last file copied and loaded I only see:

    first_file_000000000000.trn
    first_file_000000000000.trn

    So my only bet (except for restarting logshipping, is to dig through the sql log on the secondary, I guess. Unless someone has other suggestions.
  4. satya Moderator

    Or the way out is to use third party tools to read the transaction log.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. chaitu1385 New Member

    hi,

    use lumigent product log explorer (free downloadable)......... it is a very good product which gives information abt every LSN

    krishna chaitanya.s
    project engineer-SQLDBA


  6. satya Moderator

    Log explorer evaluation version works with pubs and northwind databases, you must purchase license to use other user databases.

    May try APEXSQL's log navigator also.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. dashman New Member

    I believe you can see the last LSN applied by running the following query in SQL 2005...Select
    redo_start_lsn from sys.master_files where database_id = db_id('SecondaryDatabaseName') and type = 0

Share This Page