1

I have a database with its primary file and log file stored locally. One table is split into 101 partitions which are stored on a Windows share on another server accessed by a UNC path. Each partition filegroup file is 1GB in size. Yesterday, I ran a bulk process to sequentially load data into partitions 2 through 58. The partition schema ensured that none of the files were filled to the 1GB capacity. The load did not report any errors.

Today, when I attempted to retrieve data, I began to see three distinct errors:

  1. The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x00000000026000 in file '\servername\sharename\Partition0360M'. 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.

  2. The operating system returned error 2(The system cannot find the file specified.) to SQL Server during a read at offset 0x00000000036000 in file '\servername\sharename\Partition0430M'. 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.

  3. Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

In addition, the database log reports the following in conjunction with the third error message:

A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated. with the third error message.

I was able to query all of the empty partitions (1, 59-101) without error. In addition, I was able to query partitions 25, 26, and 49-58 without error. When querying partition 39, I received the third error message each time. When querying any other partition, I typically received the first error message, but sporadically received the second error message. After repeating my queries several times, the second error message went away, and I only received the first and third error messages. [The numbering of partitions corresponds to the order in which data was inserted into them, with 2 being the first data-containing partition, and 1 being an empty partition reserved at the beginning.]

DBCC CHECKDB also failed, and it returned the following:

Msg 5901, Level 16, State 1, Line 6 One or more recovery units belonging to database 'databasename' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

Msg 1823, Level 16, State 2, Line 6 A database snapshot cannot be created because it failed to start.

Msg 1823, Level 16, State 8, Line 6 A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 6 The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Msg 5030, Level 16, State 12, Line 6 The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 6 Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

Msg 823, Level 24, State 3, Line 6 The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a write at offset 0x00000000026000 in file '\servername\sharename\Partition0570M'. 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.

In my first attempt to fix things, I rebooted the remote storage server. This did not change any of the observed behavior.

In my second attempt to fix things, I restarted the SQL Server instance. This eliminated all of the error messages mentioned above. After doing this, I was able to complete a DBCC CHECKDB successfully, and I was able to retrieve data from each partition of the table.

Two things that stood out to me while researching this:

  1. While restarting the SQL Server instance, SQL Server reported:

    795 transactions rolled forward in database 'databasename' (5:0). This is an informational message only. No user action is required.

  2. While examining logs on the virtual host of the storage server, I noticed two occurrences where device latency increased significantly and then later dropped down to normal. I lost these logs during the reboot. [If this problem occurs again, I will definitely check for a recurrence of this and update this question with the specific warning message.]

This afternoon, another developer reported the same errors on another SQL Server instance that uses this same storage server in the same manner. Restarting that SQL Server instance cleared up the errors for him, as well. In that case, the SQL Server log does not mention transactions being rolled forward, which suggests to me that that may not be related to the issues that I was seeing.

The network was functioning, since I was able to pull data from some of the partitions. Given that restarting the instances corrected the problem, there clearly was not any corruption on the remote files causing the issues. So it appears that the data was being written successfully, and it could be read successfully.

What caused these errors to occur (i.e. how do I prevent them from recurring), and if they should recur, is there a more graceful way to correct the problem?

  • 1
    A quick look over kind of tells you what caused the problems, a slow down on the shared device not servicing requests fast enough. Most of the errors about time-outs show that it took too long to get the data back or too long to get an acknowledgement. How to fix it, I'd say move the files to an appliance or share that has the proper horsepower to give results back fast. SQL Server is only going to wait so long for the operation before throwing an error. The restart "fixing it" just flushed the errors and metadata out, it actually didn't fix anything. – Sean Gallardy Nov 16 '17 at 22:35
  • @SeanGallardy-Microsoft Is there a less disruptive way to flush the errors and metadata out? – mathewb Dec 18 '17 at 20:28
  • 1
    You can attempt to offline/online the database, but if it's really having an issue communicating with the disk subsystem then all bets are off if it's going to work properly or not. – Sean Gallardy Dec 18 '17 at 20:33
  • @SeanGallardy-Microsoft That worked. I'll keep it in my back pocket. To your first point in your original comment - I came across this page, but the link under WHQL-qualified devices is dead. Could you provide a current set of standards for Microsoft-supported devices? – mathewb Dec 18 '17 at 22:06
  • The WHCL is here: sysdev.microsoft.com/en-us/hardware/lpl and the requirements for SQL Server WAL is here: support.microsoft.com/en-us/help/967576/… – Sean Gallardy Dec 18 '17 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.