Several Situation will cause the log shipping restore job failed on secondary server
1. Restore job is not able to access the transaction log backup file.
a) Other process is accessing the backup file, and lock the file, so restore job can not access it at the same time.
you can use "Process Explorer" to check which process locks the transaction log backup file
b)permission issue.
check the secondary server, make sure the SQL Agent service startup account has been granted correct permission.
2. Transaction log backup file is corrupted
Then have to rebuild the log shipping.....
3. Some log backup file is missing
All the log backup files should be consecutive. the restore job restores the log backup files based on the time stamp in backup file name. on secondary, System table msdb..log_shipping_secondary_database has a column saving the file name of last restored backup file, any log backup file with bigger time stamp in its file name will be restored, if there is log backup file missing, the lsn chain will be broken, then the restore job will be failed. In order to find out the missing file, you can:
a) on primary server, run the query below to list all log backup file history
select distinct s.first_lsn, s.last_lsn, s.backup_finish_date,y.physical_device_name
from msdb..backupset as s inner join
msdb..backupfile as f on f.backup_set_id = s.backup_set_id inner join
msdb..backupmediaset as m on s.media_set_id = m.media_set_id inner join
msdb..backupmediafamily as y on m.media_set_id=y.media_set_id
where (s.database_Name='xxx') order by s.backup_finish_date desc
b) on Secondary,
run "Restore headeronly" to check the log backup file first_lsn and Last_lsn
c) check the database last lsn on secondary server with "DBCC DBTABLE"
=======================
--Get the database last lsn
declare @str char(64),
@file bigint,
@file_size int,
@array bigint,
@array_size int,
@slot int,
@command nvarchar(400),
@end_of_log_lsn numeric(25,0),
@database_name varchar(100)
--Set the database name here
Set @database_name='mytest'
set @command = N'dbcc dbtable(' + CONVERT (nvarchar, DB_ID(@database_name)) + N') with tableresults, no_infomsgs'
declare @temp table(parentObject nvarchar(255),Object nvarchar(255),field nvarchar(255),value nvarchar(255))
insert into @temp exec( @command )
select @str = value from @temp where field like '%m_flushLSN%';
set @file_size = charindex(N':', @str)
set @file = cast( LEFT( @str, @file_size - 1) as bigint)
set @array_size = charindex(N':', @str, @file_size + 1)
set @array = cast( substring( @str, @file_size + 1, @array_size - @file_size - 1) as bigint)
set @slot = cast( substring( @str, @array_size + 1, charindex(N' ', @str, @array_size + 1) - @array_size ) as int)
set @end_of_log_lsn = @file * 1000000000000000 + @array * 100000 + @slot
-- make sure @slot < MAX_SHORT (1024 * 64)
-- check to make sure tht file and array are < 4 Gig.
if (@end_of_log_lsn < 1000000000000000 or @slot >= 65536 or @file >= 4294967296 or @array >= 4294967296)
begin
set @end_of_log_lsn = null
end
select @end_of_log_lsn as 'Last LSN'
=======================
After you get the last lsn of the database on secondary, comparing it with the log backup file first_lsn and Last_lsn to see if it is out of the backup file lsn range. if you can not find the db last lsn fit into any log backup files, then it mean there is file missing, then use the query in step a) to search which file is missing.
and bear in mind, the first thing is checking restore job history to get the detail error message, then use the steps upper to narrow down the issue.
