Creating a database in Azure Blob Storage from SQL Server running on Linux OS may fail with Msg 1802

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

If you have SQL Server 2017 running on Linux operating system and you try to create a database pointing to Azure blob storage using following statement it may fail with Msg 1802 error

 

USE master

CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.

   WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.

   , SECRET = 'sharedaccesssignature' -- this is the shared access signature token

GO

 

CREATE DATABASE test 
ON ( NAME = testdb_dat, FILENAME = 'https://<mystorageaccountname>.blob.core.windows.net/test.mdf')
LOG ON ( NAME = testdb_log, FILENAME =   ‘https://<mystorageaccountname>.blob.core.windows.net/data/log_test.ldf')  

 

Msg 1802, Level 16, State 4, Line 7

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Msg 5159, Level 24, State 5, Line 7

Operating system error (null) on file "https://<mystorageaccountname>.blob.core.windows.net/test.mdf" during MakePreviousWritesDurable.

Msg 596, Level 21, State 1, Line 6

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 6

A severe error occurred on the current command.  The results, if any, should be discarded.

 

To fix the error you have to enable trace flag 3979

sudo /opt/mssql/bin/mssql-conf traceflag 3979 on

Restart the SQL Server for the changes to reflect.

systemctl restart mssql-server.service

 

More information about trace flag 3979 can be found at https://support.microsoft.com/en-us/help/4131496/enable-forced-flush-mechanism-in-sql-server-2017-on-linux

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.