Mssql-docker: Create database on volume in (mssql-server-windows) container fails

[CPRowan]

After starting a container with this command:

docker run -d -p 51435:1433 -v C:\Users\CR037589\DockerData:U: --name sqldvlp03 --hostname sqldvlp03 -e sa_password=Passw[0]rd -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

an attempt to create a database with the files on the U: drive in the container fails with the message below:

Msg 5177, Level 16, State 1, Line 1
An unexpected error occurred while checking the sector size for file 'U:\Data-01\Data_Dev\SMSTHtndSFDB.mdf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I was able to access the U: drive in the container through a powershell session and create folders on the drive, but SQL Server can't seem to create the database files on the drive. I connect to the container using SA in order to create the database.

Seems like this should be supported, or am I doing something wrong?

[jonathanantoine]

Same issue for me, this occurs randomly while peforming my integration tests which create new database.

[rafaelcaviquioli]

Same issue for me,

[CPRowan]

Any update on this issue?

[CPRowan]

Any update on this issue?

I tried the workaround documented at https://blog.sixeyed.com/docker-volumes-on-windows-the-case-of-the-g-drive/ but got the same error.

[oneacik]

I changed container to express one.
It seems to have stopped flaking.
Maybe not solution but a clue.

[abjrcode]

We have the same problem as well. To give you some context:
We have a build server running the build in a container _(host OS is Ubuntu 18)_, the root container in turn compiles a .NET Core solution and runs xUnit tests.
Some of the tests spin up a SQL Server container.
Randomly this fails with a similar stack trace:

System.Data.SqlClient.SqlException : An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/sample-b4cff475-28eb-43a3-8e10-96409ea3e76a.mdf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
User does not have permission to alter database 'sample-b4cff475-28eb-43a3-8e10-96409ea3e76a', the database does not exist, or the database is not in a state that allows access checks.
ALTER DATABASE statement failed.
User does not have permission to alter database 'sample-b4cff475-28eb-43a3-8e10-96409ea3e76a', the database does not exist, or the database is not in a state that allows access checks.
ALTER DATABASE statement failed.
User does not have permission to alter database 'sample-b4cff475-28eb-43a3-8e10-96409ea3e76a', the database does not exist, or the database is not in a state that allows access checks.
ALTER DATABASE statement failed.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---

[kparkov]

Exactly same issue as @abjrcode here. It doesn't seem like there is any progress or acknowledgement from maintainers?

The issue is also seen randomly when using Github Actions to run tests. Changing it to Express like suggested does not solve the issue.

[xsoheilalizadeh]

I already have the same issue as @abjrcode mentioned above. I'm wondering to know is this abandoned repo, because there isn't any issue response or commits since 4 months ago.

docker-compose.yml

MSSQLServer:
  restart: always
  container_name: MSSQLServer
  image: mcr.microsoft.com/mssql/server:2019-latest
  ports:
      - 1433:1433
  environment:
      SA_PASSWORD: "#@!2020PASSword#"
      ACCEPT_EULA: "Y"
  volumes: 
    - mssqlvolume:/var/opt/mssql    

Stack Trace

 Microsoft.SqlServer.Management.Common.ExecutionFailureException : An exception occurred while executing a Transact-SQL statement or batch.
---- System.Data.SqlClient.SqlException : An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/Integration_f446f870fc604679bd97adffb440eaf1_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  Stack Trace:
     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)

docker info

WARNING: Error loading config file: /home/soheil/.docker/config.json: EOF
Client:
 Debug Mode: false

Server:
 Containers: 12
  Running: 3
  Paused: 0
  Stopped: 9
 Images: 12
 Server Version: 19.03.6
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Native Overlay Diff: true
 Logging Driver: json-file
 Cgroup Driver: cgroupfs
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
 Swarm: inactive
 Runtimes: runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: b34a5c8af56e510852c35414db4c1f4fa6172339
 runc version: 3e425f80a8c931f88e6d94a8c831b9d5aa481657
 init version: fec3683
 Security Options:
  apparmor
  seccomp
   Profile: default
 Kernel Version: 5.3.0-40-generic
 Operating System: Ubuntu 18.04.4 LTS
 OSType: linux
 Architecture: x86_64
 CPUs: 8
 Total Memory: 15.52GiB
 Name: soheil-X580VD
 ID: 5MIO:ZIJR:WS3H:RTQ6:IAW7:JB56:EMMB:VE7T:V6JZ:O4TM:AC4M:GYTX
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Registry: https://index.docker.io/v1/
 Labels:
 Experimental: false
 Insecure Registries:
  127.0.0.0/8
 Live Restore Enabled: false

WARNING: No swap limit support

docker-compose logs

2020-03-03 07:59:45.85 spid55      Error: 5177, Severity: 16, State: 1.
2020-03-03 07:59:45.85 spid55      An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/TrainIntegration_f446f870fc604679bd97adffb440eaf1_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
2020-03-03 07:59:45.85 spid55      Error: 5177, Severity: 16, State: 1.
2020-03-03 07:59:45.85 spid55      An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/TrainIntegration_f446f870fc604679bd97adffb440eaf1_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
2020-03-03 07:59:46.25 spid53      [95]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:46.26 spid53      Starting up database 'TrainIntegration_8392758913bf486ea716519586c215a9'.
2020-03-03 07:59:46.46 spid53      Parallel redo is started for database 'TrainIntegration_8392758913bf486ea716519586c215a9' with worker pool size [4].
2020-03-03 07:59:46.49 spid53      Db Id [95]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.50 spid53      Db Id [95]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.51 spid52      [93]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:46.51 spid53      Parallel redo is shutdown for database 'TrainIntegration_8392758913bf486ea716519586c215a9' with worker pool size [4].
2020-03-03 07:59:46.51 spid52      Starting up database 'TrainIntegration_210606700c68488faddcda51fee2d956'.
2020-03-03 07:59:46.68 spid52      Parallel redo is started for database 'TrainIntegration_210606700c68488faddcda51fee2d956' with worker pool size [4].
2020-03-03 07:59:46.72 spid52      Db Id [93]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.72 spid52      Db Id [93]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.74 spid52      Parallel redo is shutdown for database 'TrainIntegration_210606700c68488faddcda51fee2d956' with worker pool size [4].
2020-03-03 07:59:46.74 spid54      [94]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:46.74 spid54      Starting up database 'TrainIntegration_7291ae0e30dd4b27b22a70a6a4130270'.
2020-03-03 07:59:46.92 spid54      Parallel redo is started for database 'TrainIntegration_7291ae0e30dd4b27b22a70a6a4130270' with worker pool size [4].
2020-03-03 07:59:46.95 spid54      Db Id [94]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.96 spid54      Db Id [94]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:46.97 spid54      Parallel redo is shutdown for database 'TrainIntegration_7291ae0e30dd4b27b22a70a6a4130270' with worker pool size [4].
2020-03-03 07:59:46.98 spid57      [98]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:46.98 spid57      Starting up database 'TrainIntegration_0594b1cb29d04cdd88f48d17b9f38fe7'.
2020-03-03 07:59:47.17 spid57      Parallel redo is started for database 'TrainIntegration_0594b1cb29d04cdd88f48d17b9f38fe7' with worker pool size [4].
2020-03-03 07:59:47.20 spid57      Db Id [98]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.20 spid56      [97]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:47.20 spid57      Db Id [98]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.20 spid56      Starting up database 'TrainIntegration_7ccd9f85a3884d54ba04f4eeba3caa03'.
2020-03-03 07:59:47.21 spid57      Parallel redo is shutdown for database 'TrainIntegration_0594b1cb29d04cdd88f48d17b9f38fe7' with worker pool size [4].
2020-03-03 07:59:47.39 spid56      Parallel redo is started for database 'TrainIntegration_7ccd9f85a3884d54ba04f4eeba3caa03' with worker pool size [4].
2020-03-03 07:59:47.42 spid56      Db Id [97]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.43 spid58      [99]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:47.43 spid56      Db Id [97]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.43 spid58      Starting up database 'TrainIntegration_5879d9d0712545febed26725aecb2bd2'.
2020-03-03 07:59:47.44 spid56      Parallel redo is shutdown for database 'TrainIntegration_7ccd9f85a3884d54ba04f4eeba3caa03' with worker pool size [4].
2020-03-03 07:59:47.68 spid58      Parallel redo is started for database 'TrainIntegration_5879d9d0712545febed26725aecb2bd2' with worker pool size [4].
2020-03-03 07:59:47.70 spid58      Db Id [99]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.70 spid58      Db Id [99]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:47.72 spid58      Parallel redo is shutdown for database 'TrainIntegration_5879d9d0712545febed26725aecb2bd2' with worker pool size [4].
2020-03-03 07:59:47.98 spid55      [96]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2020-03-03 07:59:47.98 spid55      Starting up database 'TrainIntegration_f446f870fc604679bd97adffb440eaf1'.
2020-03-03 07:59:48.18 spid55      Parallel redo is started for database 'TrainIntegration_f446f870fc604679bd97adffb440eaf1' with worker pool size [4].
2020-03-03 07:59:48.20 spid55      Db Id [96]. Persistent store table, rowset id 72057594042908672, rowset id in recovery unit 72057594042908672, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:48.20 spid55      Db Id [96]. Persistent store table, rowset id 72057594042974208, rowset id in recovery unit 72057594042974208, write session partition number 1. Inserts will be optimized
2020-03-03 07:59:48.21 spid55      Parallel redo is shutdown for database 'TrainIntegration_f446f870fc604679bd97adffb440eaf1' with worker pool size [4].
2020-03-03 07:59:50.11 spid60      Process ID 52 was killed by hostname soheil-X580VD, host process ID 25649.

\cc @twright-msft, @LuisBosquez

[vin-yu]

Hi All,

2 things here:

1. We are working on a better SQL Server on Windows container story primarily focused on helping folks with dev/test/ci/cd purposes first. No timelines to share other than we are targeting in a few months.
2. There are challenges with running Linux Containers on Windows when it comes to storage. See https://docs.microsoft.com/en-us/virtualization/windowscontainers/deploy-containers/linux-containers#bind-mounts.
   While we’d love to support all the combinations of different containers and host OS. We currently only support SQL Server on Linux containers for production workloads where the container OS and host OS match. For more information on the support policy for SQL Server containers, see this document.
   https://support.microsoft.com/en-us/help/4047326/support-policy-for-microsoft-sql-server

[marionzr]

Hi all. I have a similar problem here:

2020-04-19 22:37:11.95 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'testing_e4724b99-fdcb-4ac1-9568-de8106f8b0ac'. This occurs randomly while peforming my integration tests which create new database.

e4724b99-fdcb-4ac1-9568-de8106f8b0ac is Guid.NewGuid();

I just changed my code as following and the problems have gone.
Note: I'm still doing tests
from:

var dbName = "testing_" + Guid.NewGuid()

to:

var dbName = "testing_" + (RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ?
                                Math.Abs(Guid.NewGuid().GetHashCode()).ToString() :
                                Guid.NewGuid().ToString());

Please let me know if it works for you or if we have another solution.

[ig-sinicyn]

@marionzr Thanks a lot for the workaround!
We have same issue and it was resolved after changing the DB name. Wow!

It was not resolved, unfortunately.

[ig-sinicyn]

Well, the issue is still here but it occurs not so often:(

System.Data.SqlClient.SqlException : An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/ba0bjyy_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Data:
  HelpLink.ProdName: Microsoft SQL Server
  HelpLink.ProdVer: 14.00.3294
  HelpLink.EvtSrc: MSSQLServer
  HelpLink.EvtID: 5177
  HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
  HelpLink.LinkId: 20476

[GeeWee]

Getting exact same issues running mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04 on a Fedora 31 workstation, but also on an Ubuntu agent in Azure Pipelines.

C# Stack trace:

Error Message:
   Microsoft.Data.SqlClient.SqlException : An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/oai-testdb-AlarmApiTest_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  Stack Trace:
     at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

SQL server logs:

2020-05-25 14:49:23.34 spid55      Error: 5177, Severity: 16, State: 1.
2020-05-25 14:49:23.34 spid55      An unexpected error occurred while checking the sector size for file '/var/opt/mssql/data/oai-testdb-AlarmApiTest_log.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.

Docker-compose file

version: '3.6'

services:
  db:
    image: "mcr.microsoft.com/mssql/server:2019-CU4-ubuntu-16.04"
    container_name: oai-sql-server
    restart: unless-stopped
    volumes:
      - db-data:/var/opt/mssql
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: "notMyRealPassword"
    ports:
volumes:
  db-data:

This is a major blocker for us in regards to using SQL server in dockerized CI workflows.