0

The restore is missing a backup log, and the "physical_device_name" of the missing backup log is "NUL". This is a Transaction Log that gets made out of sequence with the log shipping backup job. I can't find another service/job that triggers this job. If I understand log shipping correctly, this missing log causes the restore to fail every day at about midnight.

The bkSize is 0 but the LSN number did change after the missing backup:

enter image description here

enter image description here

I would appreciate advice to resolve this error.

Regards

Niel

4
  • Any coworkers working that late that might be testing something out? "NUL" is a special reserved device identifier that takes input from the backup, but doesn't do anything with it.
    – MrTCS
    Oct 30 '17 at 13:35
  • Currently I'm the only one working on the servers, and the restore fails every night at midnight, I'm not 100% sure that the "NUL" location backup log is the culprit or the only culprit. As far as I understand, such a extra missing log will break the log shipping chain. Any Idea where such a schedule can hiding, taking in "inputs" from the backup?
    – Niel
    Oct 30 '17 at 14:45
  • I would go off that time stamp of 23:54:51 and rummage through the sql server error log and the windows event log to somehow identify the culprit. Are there any Sql Server jobs which are running also during that time?
    – rvsc48
    Oct 30 '17 at 18:19
  • I ran a filter to see what backups are being made during that time: physical_device_name: {963E5866-DEFF-4C8E-B4BC-572153842B7B}5 and backup_start_ date is every night at around: 23:01:05.000 except Saturdays and Sundays. BackupType: Full. Is there any way that a full backup can also do a Transaction log that is part of a process. It looks like between 3 min to and 50 min after the full backup there is always that "NUL" Transaction log afterwards.
    – Niel
    Oct 31 '17 at 10:32
0

I use the following query to get a good overview of running Backups / LOG Shipping Status.

SELECT  [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            [b].[type] ,
            [b].[first_lsn] ,
            [b].[last_lsn] ,
            [b].[checkpoint_lsn] ,
            [b].[database_backup_lsn]
            --,f.media_set_id
            ,f.physical_device_name,
            script='RESTORE LOG DBNAME FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY; '
    FROM    [msdb].[dbo].[backupset] AS [b]
    LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
    WHERE   [b].[database_name] = 'DBNAME'
        AND [b].[backup_start_date] >= '2017-05-10 13:00:00.000' ORDER BY [b].[backup_start_date] asc;

It gives a picture of the Backups taken and so you will See which Backups you need to RESTORE them on secondary DB.

Sample : The Backups Marked yellow need to be restored on 2ndary, only those.

Regarding your NUL backup, have you already scripted All of the stored procedures in Place and Looked for NUL in there?

Have you observed any Logins the time this NUL Backup happens? If a login FROM outside starts this Backup you could cut the login off the Server.

Hope it helps.

enter image description here

1
  • I believe I found the problem, there was a Maintenance plan: "Log File Backups 10 min" under Maintenance Plans. I'm going to test tonight.
    – Niel
    Oct 31 '17 at 14:15

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.