SQL Server Restore Options Page – A Comprehensive Guide

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB

This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption.

This message also says that the backup set which you are trying to restore as differential backup is actually not consistent and SQL Server cannot bring database to a logical point if you try to apply this backup. The assertion checks are either SQL Server bugs or something corrupt in database.

You can bring database online and can run regular dbcc checkdb command and post the output

You need to also check your differential backup is consistent and you can do that by below command

restore verifyonly from disk='backup_location\backup.bak' 

run above command and paste the result in question

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Since database is in recovery mode you cannot run any command on it that is why every command you run on it fails

Lastly can you also add output of

sp_readerrorlog

For 1 & 2 - You need to take into account VLFs as well. Too many VLFs will slow down the restore of Transaction log.

Also, all the restores are logged in msdb--> restorefile, restorefilegroup and restorehistory tables, so many concurrent sessions will have to write to the same table and if those tables are not maintained (purged) properly, there will be contention/blocking.

As a side note, I have seen blocking especially when you logship 50+ databases with the same restore frequency.

For 3, I will just refer to the most authoritative wait stats repository.