Problems with mounted volumes in a Cluster

  • Article

As Cameron pointed out in his Blog about SAP on SQL Installation on Mount Points, there are a bunch of configurations to set to ensure the proper function of mounted volumes in a cluster. This blog is about problems, that might occur, when you do not follow the guidelines.

In the last week, during a customer visit, I came across an error message 823 in the SQL Server error log, which occurred during the shutdown of the clustered SQL Server.

2011-07-17 01:01:25.050 spid8s Error: 823, Severity: 24, State: 3.
2011-07-17 01:01:25.050 spid8s The operating system returned error 21(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x000008f67f0000 in file 'E:\LCP_Datadisk_1\LCPDATA1\LCPDATA1.mdf'. Additional messages in the SQL Server error log and sys
2011-07-17 01:01:25.050 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '2'. This is an informational message only; no user action is required.
2011-07-17 01:01:25.080 spid8s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

The shutdown of the SQL Server was either caused by a shutdown of the OS or by setting the SQL Server resource group in the Windows Failover Manager to offline. 
The given OS error 21 can be retrieved by running “net helpmsg” on a command prompt on any Windows computer:

c:\temp>net helpmsg 21
The device is not ready.

In this case the database is stored on 4 mounted volumes, which are mounted on subdirectories (LCD_Datadisk_x) on a root disk (E:).
All disks and mounted volumes were added correctly to the SQL Server Resource group:

Mounted Volumes

Each of the mounted volumes had a dependency to the root disk, what makes sense, as the root disk has to be online before the volumes can be mounted.

DataDisk Dependencies

The SQL Server Service had a dependency to the root disk and to the virtual SQL Server name:

SQL Dependencies

During the shutdown, it occurred that the mounted volumes were dismounted faster, than the SQL Server could shutdown. As the SQL Server was not able to write to the volume anymore, the error 823 was thrown. These incomplete write operation can lead to torn pages within the SQL Server database files, which is a severe corruption issue, that only can be fixed with a restore of the database.

To avoid this problem, you have to add dependencies for all mounted volumes to the dependency list of the SQL Server service. The list then has to include (at least)

  • all mounted volumes,
  • the root disk  and
  • the virtual SQL Server name.

With this setting, you ensure that the mounted volumes only can be dismounted, when the SQL Server has shutdown completely.

Running a cluster dependency report prior to the installation and after you have made major changes to the setup, is always a good idea. In the next major release of SQL Server, this issue will be covered in the installation process. The setup will set the dependencies correctly, when you install on mounted volumes. But when you move the database later onto the mounted volumes, you have to set the dependencies manually as of today.

See these Microsoft Knowledgebase articles for more details:

How to configure volume mount points on a Microsoft Cluster Server
SQL Server support for mounted volumes