1

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

migrated from stackoverflow.com Aug 18 at 10:08

This question came from our site for professional and enthusiast programmers.

  • 1
    Have you checked for a rogue BACKUP LOG BESIDE TO DISK='NUL' command? Perhaps that's inadvertently being run after FULL backups and will obviously break the chain. – Dan Guzman Aug 18 at 10:45
  • Thanks, Dan for the input. I have actually just read about backing up to "NUL" but I am not aware of any backups happening to "NUL". Is there anyway to check where different backups are happening? :) – FDelioncourt Aug 18 at 11:17
  • 1
    One method would be a filtered trace of sql_statement_started, capturing additional info like host name and application. It's likely a scheduled job so you might first review the job schedules for jobs that run around that time. – Dan Guzman Aug 18 at 12:35
2

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. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.