This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(188.79999999999998, 71%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMJ%3C/text%3E%3C/svg%3E)
Hi,
Each time i bootup my VM with SQL 2017 some of the DBs are sitting in recovery mode. This prevents our apps from starting properly. It's almost as if it's not shutdown properly or there is something wrong with the attached storage.
Thanks.
How many databases are there on the server? As I recall, first couple of databases gets attention of threads, but the next ones not.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @mike jakubik ,
Can you describe this problem more clearly? I need to collect some information.
How long will this "in recovery state" last? It is hung here or it takes a while to complete the recovery.
Confirm with you that this is not true for all databases, but some databases?
You can find the error log in a path similar to this (Take SQL Server 2019 as an example):
C: \Program Files\Microsoft SQL Server\MSSQL15.<instance name>\MSSQL\Log
You can check it out.
Best regards,
Seeya
Hi,
There are no errors in this log, just info type events. It seems to be random which DBs this happens to, we have about 15 of them. Below is a sample log for one of them. This is particularly annoying as the server is intended to be used as a DR site, so when we turn it on we need it to work ASAP.
Thanks.
2021-09-10 09:44:56.06 spid34s [INFO] HkHostDbCtxt::Initialize(): Database ID: [15] 'SMSActiveService108'. XTP Engine version is 2.11.
2021-09-10 09:44:56.45 spid34s [INFO] HkHostDbCtxt::Initialize(): Database ID: [15] 'SMSActiveService108'. XTP Engine version is 2.11.
2021-09-10 09:44:56.50 spid34s [INFO] HkHostRestoreDb(): Database ID: [15]. Starting redo controller thread on a dedicated scheduler.
2021-09-10 09:44:56.56 spid21s [INFO] Database ID: [15] SetHkTrimLsn: Bootpage HkTrimLSN updated from Old HkTrim LSN: {'000C18F8:00051920:0004'}, New HkTrim LSN: {'000C18F8:00051920:0004'}.
2021-09-10 09:44:56.57 spid21s [INFO] Database ID: [15]. Deleting unrecoverable checkpoint table row (id: 359).
2021-09-10 09:44:56.73 spid21s [INFO] HkCkptLoadInternalEx(): Database ID: [15]. Root file: {232787B6-D0DF-4815-9D49-A38D962780BF}, watermark: 16, RecoveryLsn: 000C18F8:00051920:0004, RecoveryCheckpointId: 360, RecoveryCheckpointTimestamp: 0xc59587
2021-09-10 09:44:56.76 spid21s [INFO] HkRecoverFromLogOpenRange(): Database ID: [15]. Log recovery scan from 000C18F8:00051920:0004 to 000C1908:00000878:0003.
2021-09-10 09:47:52.14 spid21s [INFO] HkRedoCloseLastOpenRangeSegment(): Database ID: [15]. Log recovery open segment scan from 000C18F8:00051920:0004 to 000C1908:00000878:0003.
2021-09-10 09:47:52.15 spid68s [INFO] redoOpenRangeSegment(): Database ID: [15]. Log recovery open segment scan completed at 000C1908:00000878:0003.
2021-09-10 09:47:52.15 spid68s [INFO] HkPrintUndoRowStats(): Database ID: [15]. Undo Rows Stats. [UndoRowsSeen] = 0, [UndoRowsMatched] = 0, [InsertRowsMatched] = 0, [InsertRowsSeen] = 0, [UndoRowsAborted] = 0
2021-09-10 09:47:52.30 spid34s [INFO] HkQuotaUpdateUserMemoryQuota(): Database ID: [15]. Updated user memory quota from 0 to 34359738368. Updated user memory to borrow from 0 to 65536.
2021-09-10 09:47:52.52 spid34s [INFO] Database ID: [15],HkHostFinalAvailabilityNotice Enter.
2021-09-10 09:47:52.52 spid34s [INFO] Database Id: [15],pru->IsReadOnly:false
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
These are all related to in-memory tables. Please make sure you have the current patches installed.
https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level
This may be related to:
https://support.microsoft.com/en-us/topic/kb4528130-fix-access-violation-occurs-when-you-restore-the-in-memory-optimized-database-in-sql-server-2016-and-2017-323e4e4c-ea50-46fa-fbc6-6f3d9758f49d
Aha, now were getting somewhere. We planned to use im memory tables but ended up not due to compatibility issues. However I can not remove these even though they are not being used due to this error "Cannot drop the last memory-optimized container" and i can not find any solution to this. SQL 2017 is on latest CU - 14.0.3401.7.
Yes, once you have created a in-memory filegroup, you cannot remove it. Not what they call a good user experience.
I can't recall that I've heard to that affecting recovery before, though,
The SQL Server error log will tell you exactly why the database is in recovery mode.
I can't find a good reason for this, it's not like the server is crashing, it goes through a clean shut down. All my windows services try to connect to these DBs before the below finishes and fail.
[INFO] redoOpenRangeSegment(): Database ID: [29]. Log recovery open segment scan completed at 000BD4AB:00000650:0003.
[INFO] HkPrintUndoRowStats(): Database ID: [29]. Undo Rows Stats. [UndoRowsSeen] = 0, [UndoRowsMatched] = 0, [InsertRowsMatched] = 0, [InsertRowsSeen] = 0, [UndoRowsAborted] = 0
[INFO] HkQuotaUpdateUserMemoryQuota(): Database ID: [29]. Updated user memory quota from 0 to 34359738368. Updated user memory to borrow from 0 to 65536.
Recovery completed for database SMSActiveService116 (database ID 29) in 143 second(s) (analysis 46 ms, redo 0 ms, undo 0 ms.) This is an informational message only. No user action is required.
Hi @mike jakubik ,
Agree with them.
You can see this document: The Memory Optimized Filegroup.
Quote from it:
Once you use a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you need to remove the memory-optimized filegroup.
Best regards,
Seeya
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(160, 61%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESC%3C/text%3E%3C/svg%3E)
Hi,
I will suggest you to read these blogs: https://blog.sqlauthority.com/2015/03/04/sql-server-database-stuck-in-in-recovery-mode-after-restart/
https://dzone.com/articles/resolving-sql-database-stuck-in-recovery-mode