Database over a network

Question

Ok I am sure most DBA will tell me this is a bad idea but my understanding is that it should work, but I think my host has an issue that is causing error 823 The operating system returned error 59(An unexpected network error occurred.)

so i am looking for ideas of what the issue might be or how to diagnose the issue.

My setup

I have a large number of Archive Database that have quite a low usage so, so we wanted to put them on cheaper storage, so the database files are available to both the live and failover servers without the need to replicate them (more space more cost) we put them on a drive that is attached to VM and mounted them over a UNC.

Initially this worked fine then we started getting the error above, the error seems to occur around midnight, it does not happen every day and we even went through a period of not seeing it at all for a few months then it came back again happening every few days.

some times it effects all the databases on the UNC some times it's only some of them.

we have extended the LanmanWorkstation session time out to more than 30 minutes and it still does it.
we are running windows 2016 and SQL 2016 Standard.

can anyone help?

Answer 1

Hi

Yes it is a network share

the full error message is
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x00000003bd0000 in file 'Path and Name of Database'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

there are 2 way we found to fix this, stop and start the sql service or force detach and reattach the database.
restart is best but not always possible.

Answer 2

Hi @Mark Dagley ,

SQL Server uses Windows APIs to perform file I/O operations, if the API calls fail with an Operating System error, then SQL Server reports ERROR 823. This error could appear in two different ways: Operating system error and Output/Input Logical Check Failed. Error 823 is a server system-level error condition that threatens database integrity and must be corrected immediately.

You can run the DBCC CHECKEDB statement when you see an 823 error. If statement does not report any errors, you probably have an intermittent system problem or a disk problem.

Review the Windows Event logs for any errors or messages reported from the Operating System or a Storage Device or a Device Driver. And use the SQLIOSim utility to find out if these 823 errors can be reproduced outside of regular SQL Server I/O requests.
When an error occurs, additional message in the SQL Server error log and system event log may provide more detail.

Or more you can do, you can refer to the following:
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-823-database-engine-error?view=sql-server-ver15

Answer 3

That is a generic "cannot connect to file" error and does not help in any way diagnose the actual problem.

I would start with your windows and VM host logs and see if there is anything. Are you are using VMWare? If so, you are likely running into this issue. https://kb.vmware.com/s/article/1002836 Server snapshots are not a replacement for backups.

Answer 4

you are using a network share accessed via a unc and wonder why sql galks? have you tried activating the 823 trace flag and see if it tells you of anything?