How to fix Recovery Pending on local SQL Server database?

Question

There is an article here that I tried following:

https://stackoverflow.com/a/52325761/22019322

It includes these steps:

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

When I get to this step:

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

GO

I get the following error:

Msg 1853, Level 16, State 1, Line 7 The logical database file 'LocalDatabaseName_log' cannot be found. Specify the full path for the file. Msg 5123, Level 16, State 1, Line 7 CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\Logs\MSSQLSERVER\LocalDatabaseName_Primary.ldf'. Msg 5024, Level 16, State 2, Line 7 No entry found for the primary log file in sysfiles1. Could not rebuild the log. Msg 5028, Level 16, State 2, Line 7 The system could not activate enough of the database to rebuild the log. DBCC results for 'LocalDatabaseName'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'LocalDatabaseName'. Msg 7909, Level 20, State 1, Line 7 The emergency-mode repair failed. You must restore from backup.

This part of the error:

while attempting to open or create the physical file 'E:\Logs\MSSQLSERVER\LocalDatabaseName_Primary.ldf'

That troubles me because I tell it to import the database to my local hard drive:

The log file exists on my hard drive, and I have a 1TB hard drive with about 700GB of free space, so space isn't an issue. Why does it keep trying to go to the mapped drive? (that drive isn't mapped on my PC - that's from the database admin who creates the backups for us)

Several times I have restored the database from a backup that I have, and this enables the SQL Server to run fine for about an hour or so, but then it reverts back to "Recovery Pending" and I cannot access the database.

UPDATE:

SergeyA commented:

Try to restore the database again with the GUI but instead of hitting OK, press Script to generate the script and add it to your question. Also put the output of the script. I want to see what happened with LocalDatabaseName_Primary.ldf. There is a way to recreate the log file but I wonder why restore didn't work as expected

SergeyA, here is the script it generated:

USE [master]
RESTORE DATABASE [LocalDatabaseName]
FROM  DISK = N'C:\Users\CTCRRMCITJP\LocalDatabaseName_Dev_2023_08_24_02_00_00.bak'
WITH  FILE = 1, 
MOVE N'LocalDatabaseName' TO N'C:\Users\CTCRRMCITJP\LocalDatabaseName_Primary.mdf', 
MOVE N'LocalDatabaseName_log' TO N'C:\Users\CTCRRMCITJP\LocalDatabaseName_Primary.ldf', 
NOUNLOAD, 
REPLACE, 
STATS = 5

GO

I did edit the script to include carriage returns because it was so long.

The output of running this statement is as follows:

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 123840 pages for database 'LocalDatabaseName', file 'LocalDatabaseName' on file 1.
Processed 22 pages for database 'LocalDatabaseName', file 'LocalDatabaseName_log' on file 1.
RESTORE DATABASE successfully processed 123862 pages in 1.076 seconds (899.322 MB/sec).

Completion time: 2023-09-01T09:26:05.6448115-05:00

Does this shed light on anything?

Again, the database doesn't go into "Recovery Pending" until it has sat there for an hour or so.

Perhaps this is worth noting: I have 2 databases. Before, both of them were going into Recovery Pending mode. At that time, I was using SQL Server Express, and someone from work suggested I get SQL Server Developer. Now the 2nd database seems to be stable, but this one is still having issues.

UPDATE 2:

GAURAV RATHOD commented:

check sp_readerrorlog to see if something happens right before/after DB goes into recovery pending

Here is the log file, but I don't see anything suspicious. Do you?

13:46.0 Server  "Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)   Jul 19 2021 15:37:34    Copyright (C) 2019 Microsoft Corporation    Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor) "
13:46.0 Server  UTC adjustment: -5:00
13:46.0 Server  (c) Microsoft Corporation.
13:46.0 Server  All rights reserved.
13:46.0 Server  Server process ID is 9860.
13:46.0 Server  "System Manufacturer: 'HP', System Model: 'HP ZBook Fury 15.6 Inch G8 Mobile Workstation PC'."
13:46.0 Server  Authentication mode is MIXED.
13:46.0 Server  Logging SQL Server messages in file 'C:\Users\CTCRRMCITJP\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\msSqlLocalDB\error.log'.
13:46.0 Server  The service account is 'OPM\CTCRRMCITJP'. This is an informational message; no user action is required.
13:46.0 Server  "Command Line Startup Parameters:    -c      -S ""MSSQL15E.LOCALDB""     -s ""LOCALDB#90B137F6""     -d ""C:\Users\CTCRRMCITJP\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\msSqlLocalDB\master.mdf""     -l ""C:\Users\CTCRRMCITJP\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\msSqlLocalDB\mastlog.ldf""    -e ""C:\Users\CTCRRMCITJP\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\msSqlLocalDB\error.log"""
13:46.0 Server  "SQL Server detected 1 sockets with 8 cores per socket and 16 logical processors per socket, 16 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required."
13:46.0 Server  SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
13:46.0 Server  Detected 65219 MB of RAM. This is an informational message; no user action is required.
13:46.0 Server  Using conventional memory in the memory manager.
13:46.0 Server  Page exclusion bitmap is enabled.
13:46.1 Server  Buffer Pool: Allocating 16777216 bytes for 9131500 hashPages.
13:46.2 Server  Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
13:46.2 Server  Buffer pool extension is already disabled. No action is necessary.
13:46.2 Server  "Query Store settings initialized with enabled = 1, "
13:46.2 Server  The maximum number of dedicated administrator connections for this instance is '1'
13:46.2 Server  Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
13:46.3 Server  Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
13:46.3 Server  Lock partitioning is enabled.  This is an informational message only. No user action is required.
13:46.3 Server  In-Memory OLTP initialized on standard machine.
13:46.3 Server  [INFO] Created Extended Events session 'hkenginexesession'
13:46.3 Server  Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
13:46.3 Server  Total Log Writer threads: 2. This is an informational message; no user action is required.
13:46.3 Server  clwb is selected for pmem flush operation.
13:46.3 Server  Software Usage Metrics is disabled.
13:46.3 spid10s Starting up database 'master'.
13:46.4 spid10s 10 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
13:46.4 spid10s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
13:46.4 spid10s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
13:46.4 Server  CLR version v4.0.30319 loaded.
13:46.4 spid10s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
13:46.5 spid10s SQL Server Audit has started the audits. This is an informational message. No user action is required.
13:46.5 Server  Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
13:46.5 spid10s "SQL Trace ID 1 was started by login ""sa""."
13:46.5 spid10s Server name is 'OL-2TK1500CBP\LOCALDB#90B137F6'. This is an informational message only. No user action is required.
13:46.5 spid19s Server local connection provider is ready to accept connection on [ \\.\pipe\LOCALDB#90B137F6\tsql\query ].
13:46.5 spid19s "Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required."
13:46.5 spid19s SQL Server is now ready for client connections. This is an informational message; no user action is required.
13:46.5 spid14s Starting up database 'mssqlsystemresource'.
13:46.5 spid14s The resource database build version is 15.00.4153. This is an informational message only. No user action is required.
13:46.5 spid24s Starting up database 'OPM.Staffing.Data.USASContext '.
13:46.5 spid23s Starting up database 'LocalDatabaseName_Primary'.
13:46.5 spid22s Starting up database 'msdb'.
13:46.5 spid23s "Error: 1853, Severity: 16, State: 1."
13:46.5 spid23s The logical database file 'LocalDatabaseName_Primary_log' cannot be found. Specify the full path for the file.
13:46.5 spid14s Starting up database 'model'.
13:46.5 spid22s 1 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required.
13:46.6 spid22s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required.
13:46.6 spid22s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
13:46.6 spid24s Parallel redo is started for database 'OPM.Staffing.Data.USASContext ' with worker pool size [4].
13:46.6 spid24s 71 transactions rolled forward in database 'OPM.Staffing.Data.USASContext ' (6:0). This is an informational message only. No user action is required.
13:46.6 spid14s Clearing tempdb database.
13:46.6 spid24s 0 transactions rolled back in database 'OPM.Staffing.Data.USASContext ' (6:0). This is an informational message only. No user action is required.
13:46.6 spid24s Parallel redo is shutdown for database 'OPM.Staffing.Data.USASContext ' with worker pool size [4].
13:46.6 spid14s Starting up database 'tempdb'.
13:46.7 spid23s The Service Broker endpoint is in disabled or stopped state.
13:46.7 spid23s The Database Mirroring endpoint is in disabled or stopped state.
13:46.7 spid23s Service Broker manager has started.
13:46.8 spid10s Recovery is complete. This is an informational message only. No user action is required.
13:47.3 spid51  Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
13:47.3 spid51  Using 'xplog70.dll' version '2019.150.4153' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
13:58.9 spid53  Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
13:58.9 spid53  Using 'xpstar.dll' version '2019.150.4153' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required. 

Answer 1

So here is my point of view You successfully restored the log file to 'E:\Logs\MSSQLSERVER\LocalDatabaseName_Primary.ldf' but later SQL server was not able to find it and the database goes to recovery pending mode.

I don't know how can you do it.

On the second time (via script) you restored the log file to correct location 'C:\Users\CTCRRMCITJP\LocalDatabaseName_Primary.ldf'

If the file exists on C:\Users\CTCRRMCITJP\LocalDatabaseName_Primary.ldf

Try to inform SQL server about old location

alter database LocalDatabaseName
 MODIFY FILE ( NAME = LocalDatabaseName_log,
FILENAME = 'C:\Users\CTCRRMCITJP\LocalDatabaseName_Primary.ldf' )