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
,b.physical_device_name
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.
