SQL Server 823 Error - Local installation

 

 I have a local installation of SQL Developer Edition on my laptop and I’ve noticed that I’ve been getting a series of 823 errors recently and I’m attributing that to having the SQL installation on C: a solid state drive and the database files on D: a spindle.  I ‘m confident that the SQL service starts up on boot as expected and then when it tries to connect to D: has a failure as the solid state and service is ready before the spindle.

 

The error looks like:

 

If you try to attach a database you’ll see it doesn’t find the drive and remains greyed out.

 

 

Any actions you attempt will result in the same error as there’s no master db.

 

The main text of the error is:

  TITLE: Microsoft SQL Server Management Studio
------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000272000

 

The fix.  The first thing you can try is to simply restart the SQL Server Service.  Navigate to the services window and down to SQL Server(MSSQLSERVER) right click and restart.

 

 

 

Let it stop then start the service with right click restart or stop then start. 

 

You could also use a script if you want to have something quick and aren’t planning additional mitigation steps.  Here’s a sample Powershell script

Stop-Service -f MSSQLSERVER
Start-Sleep -s 20
Start-Service MSSQLSERVER
get-service MSSQLSERVER

 

 

 

Check and see if it worked by refreshing the database list in SSMS.

 

For me this is effective.  As a mitigation I set the SQL service to delayed start.  The delay is hard to pin down but for me it seems to be about 1-2 minutes after boot and setting this to delayed doesn’t seem to cause problems with the other SQL services.  I’m working on researching dependencies but if you have any issues or other resources I’d love to know.