0

I am having issues with the local databases on SQL Server on my Windows 10 machine going into “Pending Recovery”. This appears random but happens more than once per day. When one database goes into “Pending Recovery” 3 or 4 out of the other ten on my machine generally go into the state as well, even if I am not using them.

From what I have found online, the general fix for this is to fun the following scrip:

USE master
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'myPassword' 
--CLOSE MASTER KEY

ALTER DATABASE [MYDBNAME] SET EMERGENCY;
GO
ALTER DATABASE [MYDBNAME] set single_user
GO
DBCC CHECKDB ([MYDBNAME], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [MYDBNAME] set multi_user
GO

The script above sometimes works, but the problem has come back and over the last six weeks it has fixed the problem much less often and I have had to restore from fresh backups instead.

This is what I have tried:

  • Run chkdsk in command prompt as admin (no errors found)
  • Manually restore the database from a backup. This is a backup the other members of my team have used with no issues, and initially the restore works and I can use the database.
  • Fully reinstall SQL Server more than five times (I also physically delete C:\Program Files\Microsoft SQL Server folder and all AppData related to SQL)
  • Look at Event Log (Nothing different than what SSMS tells me when the script above fails)
  • I appear to have enough free space on my drive and have not had any space issues

I have had my computer about two years and this started happening about 3 months ago. I am a local admin.

My guess is it is a disk issue of some sort but I am not very knowledgeable about that sort of thing.

I am using a Dell 5560 Precision and I get a bluescreen every 2 to 4 weeks but have not been able to determine the reason. There has been nothing in the event log related to the crash.

The only last idea I have is to reformat the hard drive and try reinstalling everything including Windows and see if the problem reoccurs but I suspect that it will. Is there anything else I can do to troubleshoot why this keeps happening? Thanks in advance for any help or suggestions on how to further research this.

4
  • 3
    If you're getting a BSOD that's likely the reason why you're having other issues. Windows should be creating a MEMORY.DMP file for the BSOD, if you want fell free to upload that somewhere and send me a link in chat. Commented Nov 18, 2022 at 17:44
  • ^This. Also when you say "Look at Event Log", do you mean the Windows Event Log or the SQL Server Logs?
    – J.D.
    Commented Nov 18, 2022 at 19:46
  • Corruption is most likely from: memory problems (run Windows Memory DIagnostic to check), buggy drivers (ensure Windows and all your drivers are up to date, as well as antivirus), malware (run antivirus scan that looks for malware drivers), disk fault (check SMART report and RAID), also on consumer SSD drives there is no Power Loss Protection, which can cause a messed up page allocation in the event of a BSOD or power failure. Commented Nov 21, 2022 at 1:34
  • 1
    @J.D. Im surprised I forgot to check the SQL logs as well, thank you. Sean I will check Memory.DMP next time I have a crash (currently empty). Charlieface great idea I will work on what you suggest today. Thank you. Commented Nov 21, 2022 at 15:03

1 Answer 1

1

It turns out I had run an old encryption script after a restore that had disabled encryption, so when I rebooted the master key was not open on the server. It is strange that did not show up more directly in the logs but I was able to fix the issue by running the script below.

alter master key add encryption by service master KEY

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.