When attempting to run dbcc checkdb or create a snapshot database you may encounter the following errors when using a UNC location that does not support all sparse file operations.

2010-09-21 17:27:26.47 spid82      Error: 17053, Severity: 16, State: 1.

2010-09-21 17:27:26.47 spid82      SidePageTable::Init() DeviceIoControl() : Operating system error 1(Incorrect function.) encountered.

2010-09-21 17:27:26.47 spid82      Error: 17204, Severity: 16, State: 1.

2010-09-21 17:27:26.47 spid82      FCB::Open failed: Could not open file \\MyServers\smb2\MSSQL10.MSSQLSERVER\MSSQL\DATA\MYDB.mdf:MSSQL_DBCC11 for file number 1.  OS error: 1(Incorrect function.).

This error was encountered on a NAS device.  Specifically, the SidePageTable initialization is a call to the Windows API DeviceIoControl using FSCTL_QUERY_ALLOCATED_RANGES.

The following is an example of the call used by SQL Server.

    retcode = DeviceIoControl (

                handle,

                FSCTL_QUERY_ALLOCATED_RANGES,

                &startRange,

                sizeof (startRange),

                ranges,

                sizeof (ranges),

                &bytesReturned,

                NULL);

            error = GetLastError ();      

 

http://msdn.microsoft.com/en-us/library/aa364582(VS.85).aspx


If you are encountering this issue contact your hardware manufacture to obtain the proper device and driver updates.

Note:  SQL Server 2008 R2 supports databases on SMB 2.0 compliant devices.  The SQLIOSim.exe that ships with SQL Server 2008 R2 has been updated to allow testing against a UNC location.   Previous versions of SQLIOSim.exe failed with an error when calling the Windows API GetVolumeNameForVolumeMountPoint.

Currently SQLIOSim.exe can test sparse file implementations but does NOT attempt the FSCTL_QUERY_ALLOCATED_RANGES and as such will not expose the issue that could be encountered by dbcc checkdb or snapshot database creations.

Bob Dorr - Principal SQL Server Escalation Engineer