Error 17053 when using third party network storage device / SMB file share

[mssql-support]

First published on MSDN on Jul 21, 2015
Starting SQL Server 2012, we support creating database on a remote SMB file share without requiring a trace flag.  You can even configure SQL cluster to use SMB file share for databases. This is documented here .

When creating or opening data or log files, SQL Server calls various file manipulation API including an WIN32 API called DeviceIoControl to send commands to the device driver for various operations.

I want to bring your attention that not all third party SMB device supports all device IO Control code used by SQL Server when calling API DeviceIoControl .

Recently we worked with a customer who was configuring to use a third party network attached storage device to store all their databases remotely on an SMB share.

They kept receiving the following error whenever they perform the following:

1. restarting SQL Server
2. creating a new database
3. marking a database online

2015-06-04 13:14:19.97 spid9s      Error: 17053, Severity: 16, State: 1.
2015-06-04 13:14:19.97 spid9s      DoDevIoCtlOut() GetOverlappedResult() : Operating system error 1(Incorrect function.) encountered.


Through debugging, we learned that the failure came when SQL Server calls DeviceIoControl using device control code FSCTL_FILESYSTEM_GET_STATISTICS.  This API is called for creating and opening any data or log file.

But this particular third party device driver doesn’t  support device io control code FSCTL_FILESYSTEM_GET_STATISTICS.  In other words,  the error occurred because the storage driver doesn’t support it device code FSCTL_FILESYSTEM_GET_STATISTICS in windows API DeviceIoControl call.

If you experience similar errors as above with third party device, first test to see if you can create database on Windows file share without error.  If you can do it with Windows file share but not vendor’s SMB share, please contact your storage vendor to get verification on what they support.

A few very important notes:

1. If the device doesn’t support io code FSCTL_FILESYSTEM_GET_STATISTICS, there are different rampifications depending on which file System you use

• If you are using NTFS with SQL Data and log files, this error can be safely ignored
• But if you are using ReFS file system, ignoring this error can lead serious performance degradation as many optimizations will be skipped by SQL Server.  SQL 2014 and above support ReFS (Resilient File System)

Not all 17053 error is the same.  In this case, we debugged and discovered it’s the io code FSCTL_FILESYSTEM_GET_STATISTICS that’s not supported.   the same error can be raised by other situations.   The situation described here is very specific to a particular device vendor that supports SMB share with their devices for SQL Server.  If you are not clear what to do, please open a support call with us.

References

1. DeviceIoControl API: https://msdn.microsoft.com/en-us/library/windows/desktop/aa363216(v=vs.85).aspx
2. Device code FSCTL_FILESYSTEM_GET_STATISTICS: https://msdn.microsoft.com/en-us/library/windows/desktop/aa364565(v=vs.85).aspx
3. SQL Server support of SMB file share: https://msdn.microsoft.com/en-us/library/hh759341(v=sql.120).aspx

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter | pssdiag | Sql Nexus