Sql-server – Log shipping always failing with NUL

log-shippingsql server

I have configured a log shipping for our company's primary database to restore on a separate server, with the restored database serving as report database.

The problem is that every few days, the log shipping breaks and unfortunately, more often than not, a full backup happens after the break which prevents me from doing a differential restore to "un-break" the LSN chain. Here are the results of the LSN chain for your reference (primary server and database):

LSN Checkpoints

Log shipping breaks at the red marker

I can usually restart the LSN chain if I can do differential restore before the full backup happens but I should not really depend on that. The main issue here is I am getting a "NUL" message in the following query, and I am 100% sure that the LSN breaks where the "NUL" happens, as the last restored log is the one with the green highlight. I would really appreciate anyone's input. Thanks.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'BESIDE'

SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'  AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
  THEN 'Full'
  WHEN 'I'
  THEN 'Differential'
  WHEN 'L'
  THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date

Best Answer

First of all, thanks to @Dan Guzman for his inputs, somehow it sparked something in my head. :)

I found out the reason for the break in the LSN chain! As I mentioned, the chain breaks after a backup is made to 'NUL', and I just came to know that 'NUL' is a sort-of file system, where data is written to it but then deleted afterwards. So obviously, if a transaction log is written to 'NUL' it creates a valid LSN but then since it gets deleted it cannot be restored to the secondary database and therefore breaking the LSN chain.

Now, i have this quote from this white paper,

If you select “Truncate logs” inside the SQL tab then Veeam will perform a log backup to the file name “nul” after the snapshot was produced. It will do this for all databases that are in full or bulk logged recovery model. This will render your own log backups performed after this useless.

So it is a third-party backup software, VEEAM, that is causing the issue. Right now, what I did was I still let VEEAM manage its own backups of the primary database, but disable VEEAM from truncating logs and let it just copy the transaction logs instead. Problem solved, now my log shipping is running without a hitch. :)

Related Question