I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got failed continuously after T-log truncation.

I used the following query to find the history of backup taken for a DB.

select  top 60 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where a.database_name Like 'ADMIN%'
order by a.backup_finish_date desc

As a result of above query, i able to find that LOCATION is NULL for truncated T-Log file. Due to this LSN Mismatch is occurring while performing restore.

We are unable to find that why it is truncating and appearing as NULL. Is there is any solution to stop truncating the T-LOG File.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.