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:
- restarting SQL Server
- creating a new database
- 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:
- 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)
- DeviceIoControl API: https://msdn.microsoft.com/en-us/library/windows/desktop/aa363216(v=vs.85).aspx
- Device code FSCTL_FILESYSTEM_GET_STATISTICS: https://msdn.microsoft.com/en-us/library/windows/desktop/aa364565(v=vs.85).aspx
- 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