SQL Server 2016 – Log Shipping Restore Job Stuck

If the log backup's are able to be restored while the secondary database is in NORECOVERY and only fails when it is in READ-ONLY/STANDBY then I would assume the log backups themselves are ok and not corrupted.

It could be that your reporting component has an open connection to the database therefore when restoring the log file it is unable to obtain an exclusive connection to the database due to the open connections. There would be an option when setting up log shipping to disconnect any connections to allow it to restore the log backup.

It just seems so fragile.

Logshipping is tested and proved since sql server 2000 (and even older) days. Its not fragile.

Look at the errors ...

Last Restored File: \server\folder\db_20160105060002.trn,

Logshipping is trying to restore

Destination: '\server\folder\db_20160105080001.trn'

This means you have a gap in the log sequence. There might be adhoc log backups happening which is breaking the log chain.

Refer to my answer - How does Log shipping knows to keep track.

You can even Restrict users to COPY ONLY log backups, so that adhoc log backups wont break the log chain. Also,

@Spörri made a valid point to disable SQL VSS writer service, so that 3rd party backup tool cannot interact with SQL. Its a pain to find that out, since 3rd party softwares are crazy sometimes !

To find out gaps in your log backups, you can use below query

SELECT 
    s.database_name,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 = 'databaseNamePrimaryServer')
ORDER BY 
    s.backup_finish_date DESC;

Another useful query:

-- http://sqlblog.com/blogs/tibor_karaszi/archive/2014/11/03/can-you-restore-from-your-backups-are-you-sure.aspx
-- modified by Kin to include backup start and finish dates
SELECT TOP(100)
database_name
,CASE bs.TYPE
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file '
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,bs.backup_start_date
,bs.backup_finish_date
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
  INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id  
  where database_name = 'master' -- change here for your database 
ORDER BY backup_finish_date DESC;