question

mikejakubik-9988 avatar image
0 Votes"
mikejakubik-9988 asked StacyClark-5656 answered

Dbs in recovery state on bootup

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.

sql-server-general
· 8
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.

How many databases are there on the server? As I recall, first couple of databases gets attention of threads, but the next ones not.

0 Votes 0 ·

Hi @mikejakubik-9988,

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

0 Votes 0 ·

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

The SQL Server error log will tell you exactly why the database is in recovery mode.

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.

mikejakubik-9988 avatar image
0 Votes"
mikejakubik-9988 answered mikejakubik-9988 edited

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.

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.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @mikejakubik-9988,

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.

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.

StacyClark-5656 avatar image
0 Votes"
StacyClark-5656 answered
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.