question

66774741 avatar image
0 Votes"
66774741 asked Cathyji-msft commented

A severe error occurred after enabling and disabling ADR while deleting rows and rollbacking transaction

The following commands always causing for me a system assertion check failure:

 use master
 alter database MyDatabase set ACCELERATED_DATABASE_RECOVERY = ON;
 use MyDatabase 
 begin tran
 delete MyTable where id < 5000
 rollback
 use master
 alter database MyDatabase set ACCELERATED_DATABASE_RECOVERY = OFF;
 use MyDatabase
 begin tran
 delete MyTable  where id < 5000
 rollback

The following assertion check is displayed on error:

 (4999 rows affected)
 Location:     sql\ntdbms\storeng\include\page.inl:1067
 Expression:     sid >= m_slotCnt || m_slots[-sid].GetOffset () ==0
 SPID:         58
 Process ID:     16204
 Msg 3624, Level 20, State 1, Line 45
 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. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
 Msg 3314, Level 21, State 3, Line 45
 During undoing of a logged operation in database 'testABCD121121212' (page (1:587) if any), an error occurred at log record ID (41:15752:352). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database.
 Msg 3314, Level 21, State 5, Line 45
 During undoing of a logged operation in database 'testABCD121121212' (page (0:0) if any), an error occurred at log record ID (41:4712:1). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database.
 Msg 596, Level 21, State 1, Line 32
 Cannot continue the execution because the session is in the kill state.
 Msg 0, Level 20, State 0, Line 32
 A severe error occurred on the current command.  The results, if any, should be discarded.

It also depends on how many database records deleted. With fewer records, the error happens rarely but with 5000 or more its happens every time for me. It doesn't work if there any other command instead of the first delete command but the second delete command can be any command that changes rows.
After the error database turns into suspect mode and I unable to recover data from it. Is there any fix for this or any way to repair the database?
DBCC seems to be unable to repair it. It is likely to be caused by two identical FORWARDING_STUB records pointing to the same FORWARDED_STUB record in the database file.









sql-server-generalsql-server-transact-sql
· 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 @66774741,

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

What is the output from "SELECT @@version"?

If you run DBCC CHECKDB on the database, before you try the operation, does it come out clean?

Generally, assertion errors are due to bugs in the product, unless there is corruption involved.

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

Hi @66774741,

ADR is new in SQL Server 2019. ADR is off by default in SQL Server 2019 (15.x). We should consider ADR when we are the following types of customers.

• Customers that have workloads with long running transactions.
• Customers that have seen cases where active transactions are causing the transaction log to grow significantly.
• Customers that have experienced long periods of database unavailability due to SQL Server long running recovery (such as unexpected SQL Server restart or manual transaction rollback).

Refer to MS document Accelerated database recovery.

After the error database turns into suspect mode and I unable to recover data from it. Is there any fix for this or any way to repair the database?

We can using the following steps to repair the database in suspect mode;

  1. ALTER DATABASE databasename SET EMERGENCY

  2. DBCC checkdb(databasename)

  3. ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  4. DBCC CheckDB (databasename, REPAIR_ALLOW_DATA_LOSS)

  5. ALTER DATABASE databasename SET MULTI_USER

If you have the backup files for the database, you can restore the database from backup.

Refer to the blog Procedure to Recover SQL Database from SUSPECT Mode to get more detail information.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


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.