Core and Master databases keep falling under recovery_pending state

Question

We are on an upgraded instance and restoring Core and Master databases from an already running environment to a new one we are building.

After the DB restore as soon as we start the app pool and trying to connect Sitecore 9.0.2 instance with these DBs, the status of databases is changing to recovery_pending. We see the following exceptions in logs as well as on browser.

We confirm there are no space issues on the SQL server, Auto Grow is turned on, tried offline/online databases and the ldf/mdf are not read-only.

Any inputs are highly appreciated.

ERROR Exception processing remote events from database: master Exception: System.Data.SqlClient.SqlException Message: Database 'Sitecore_Master_stg' cannot be opened due to inaccessible files or insufficient memory or disk space.

Answer 1

Disclaimer: Since it turned out to be an access violation issue on SQL server and not related to Sitecore not sure if this question belongs to this community, I leave that up to the moderators. 😊

Issue Summary:

After a restart of SQL Server 2016, Core and Master databases are online and accessible on SQL Server. When the application starts, these two databases go into recovery pending state and cannot be accessed. Once SQL Server is restarted again they come back online. Each time the databases go into recovery pending, SQL Server issues a stack dump. After review of the dump, I have found an EXCEPTION_ACCESS_VIOLATION in function CSecCtxtCacheStore. We see the following error logs:

2019-04-15 11:35:15.00 spid67      SqlDumpExceptionHandler: Process 67 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2019-04-15 11:35:15.00 spid67      * *******************************************************************************
2019-04-15 11:35:15.00 spid67      *
2019-04-15 11:35:15.00 spid67      * BEGIN STACK DUMP:
2019-04-15 11:35:15.00 spid67      *   04/15/19 11:35:15 spid 67
2019-04-15 11:35:15.00 spid67      *
2019-04-15 11:35:15.00 spid67      *
2019-04-15 11:35:15.00 spid67      *   Exception Address = 00007FFD7876F4AE Module(sqllang+000000000000F4AE)
2019-04-15 11:35:15.00 spid67      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2019-04-15 11:35:15.00 spid67      *   Access Violation occurred reading address 0000000000001278

An Access Violation (AV) occurs when a program performs an action on a memory address however that action is not allowed. Memory page will be set with proper memory protection option during allocating or protecting calls. If an application access a memory address which does not align with the page protection for that memory, Access Violation exception will be thrown.

The most common AV pattern will be a read or write to address 0 (zero). A common programming issue involves several scenarios where the programmer expects a value to be a valid address, but for whatever reason it did not get set properly (e.g. a function failed and returned 0 or null instead of the expected value).

Resolution:

We raised a case with Microsoft Support and they identified it as a known problem with SQL Server 2016. Support suggested us to apply a cumulative update which resolved the issue:

https://support.microsoft.com/en-us/help/4488536/cumulative-update-6-for-sql-server-2016-sp2

Answer 2

Just replace your database name and run below script on SQL:

ALTER DATABASE [DBName] SET EMERGENCY;

GO

ALTER DATABASE [DBName] set single_user

GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

GO

ALTER DATABASE [DBName] set multi_user

GO

Answer 3

Perform below steps:

1. Take backup of your SQL Database files (.mdf and .ldf) from windows installation folder path (C:\Program Files\Microsoft SQL Server\MSSQL13.SA\MSSQL\DATA).
2. Remove database from SQL server.
3. Attached/Restore database again from backup files (steps 1)

Thanks.