SQL Server Logshipping is failing

Question

Hi All,'

Logshipping is created on SQL 2012, but it is often fails due to 'Out of sync'. Even if I take a full backup of the primary database and restored on secondary database, it fails when logshipping runs next time.
I was queried the backup history, when I am getting 'NUL' for physical_device_name. Is logshipping going out of sync due to this?

Please advice, thanks.

Answer 1

I was queried the backup history, when I am getting 'NUL' for physical_device_name. Is logshipping going out of sync due to this?

Yes correct, someone is running command line backup database db_name to disk='nul' which makes SQL Server think that you are actually taking backup but since you would have specified NUL (Single L) the backup goes into kind of space which immediately deleted it, it like NUL which you use in Unix which deletes anything written on it. So SQL Server thinks you took backup and stored and OTOH you lost it in NUL and this log backup is not moved to secondary server hence LS breaks.

You need to find this process and STOP it, its NOT needed at all

Answer 2

Hi @Naveena Jaineera ,

For me from the sreen shot it looks like backups [both full & log] are configured to happen through some third pary tool and as well as to local drives (native backups) for the database in question.

Could you please check and verify on that.

Thanks.

Answer 3

Hi @Naveena Jaineera ,

>I was queried the backup history, when I am getting 'NUL' for physical_device_name. Is logshipping going out of sync due to this?

Yes, you are right. '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.

Suggest you remove your log shipping , then reconfigure log shipping. Please clear the Enable this as a primary database in a log shipping configuration check box. Click OK to remove log shipping from this primary database. Refer to MS document Remove Log Shipping (SQL Server).

Before you configure log shipping, suggest you create a share to make the transaction log backups available to the secondary server. Then re-configure log shipping. Refer to MS document Configure Log Shipping (SQL Server).

Please make sure no other log backup job occurred on this database, except log shipping backup job. Other log backup job will also break the LSN chain.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 4

Issue got resolved by disabling service 'SQL Server VSS Writer'.

Now I am not getting an auto entry 'NUL' in the SQL backup/restore history and hence there there is no LSN chain break.