question

NaveenaJaineera-9056 avatar image
0 Votes"
NaveenaJaineera-9056 asked NaveenaJaineera-9056 answered

SQL Server Logshipping is failing

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.

78762-image-2021-03-16t06-11-47-458z.png


sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NaveenaJaineera-9056,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered Shashank-Singh edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TheDBLearningGuy avatar image
0 Votes"
TheDBLearningGuy answered

Hi @NaveenaJaineera-9056,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @NaveenaJaineera-9056,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaveenaJaineera-9056 avatar image
0 Votes"
NaveenaJaineera-9056 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.