0

lately one of our SQL server 2019 database has been going 'In recovery' after a restart. The databases aren't particularly large. The server has 8 databases of ~17GB per database. The database itself is a Azure Standard DS12 v2 (4 vcpus, 28 GiB memory) machine which is more then sufficient for our workloads.

The steps:

  • Take a full back-up
  • Run migration scripts for our software release
  • Take a full back-up
  • Execute Windows updates
  • Machine reboots, (5 out of 8) databases stays in "In recovery"

Our database is pretty standard. We do make use of a WebJob which is continuously connected via the SQL service broker to run batch tasks. We also have multiple memory optimized tables.

Although the databases are in recovery. The SQL server instance doesn't seem to be doing anything (activity monitor shows nothing running). The CPU and Disk usage of the machine is minimal. There are no errors or warnings appearing the SQL server logs or event viewer. We've already waited +24 hours, and the databases remain in "In recovery".

We currently have a work-around to delete the Db files. And restore the full back-up from after the release. This isn't causing us data loss. But it does put a lot of extra time and effort in our release. And it feels very unstable.

We are currently creating a plan to monitor the contents of our log files. But I was hoping someone here, might have some other tips on resolving this issue.

4
  • Please, check errorlog for more details about not recovered databases. It can be done also by the following command: exec xp_readerrorlog 0,1,N'databasename' Dec 21, 2021 at 14:12
  • Nothing shows up. The recovery starts according to the log, but nothing seems to be happening
    – Reinard
    Dec 21, 2021 at 14:39
  • Try this script, if it returns anything: mssqltips.com/sqlservertip/4832/… Dec 21, 2021 at 14:59
  • thanks, @AndreySamykin. We currently aren't experiecing the issue anymore since we reduced our memory optimized tables. But I'll see we can still reproduce it and see if this provides us with any additional information
    – Reinard
    Mar 18, 2022 at 8:34

1 Answer 1

0

The behaviour is caused by performance degradation by the +400 memory optimized table we have in our database. We removed all memory optimized tables by reworking our framework, and databases are restoring within minutes now.

What is exactly causing the problem within memory optimized tables hasn't been found yet. But due to resource constraints, we'll need to delay further investigation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.