Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1102576%22%20slang%3D%22en-US%22%3ECreating%20a%20database%20in%20Azure%20Blob%20Storage%20from%20SQL%20Server%20running%20on%20Linux%20OS%20may%20fail%20with%20Msg%201802%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102576%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20have%20SQL%20Server%202017%20running%20on%20Linux%20operating%20system%20and%20you%20try%20to%20create%20a%20database%20pointing%20to%20Azure%20blob%20storage%20using%20following%20statement%20it%20may%20fail%20with%20Msg%201802%20error%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EUSE%20master%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3ECREATE%20CREDENTIAL%20%5Bhttps%3A%2F%2F%3CMYSTORAGEACCOUNTNAME%3E.blob.core.windows.net%2F%3CMYSTORAGEACCOUNTCONTAINERNAME%3E%5D%20--%20this%20name%20must%20match%20the%20container%20path%2C%20start%20with%20https%20and%20must%20not%20contain%20a%20trailing%20forward%20slash.%3C%2FMYSTORAGEACCOUNTCONTAINERNAME%3E%3C%2FMYSTORAGEACCOUNTNAME%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%26nbsp%3B%20WITH%20IDENTITY%3D'SHARED%20ACCESS%20SIGNATURE'%20--%20this%20is%20a%20mandatory%20string%20and%20do%20not%20change%20it.%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%26nbsp%3B%20%2C%20SECRET%20%3D%20'sharedaccesssignature'%20--%20this%20is%20the%20shared%20access%20signature%20token%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EGO%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3ECREATE%20DATABASE%20test%E3%80%80%3CBR%20%2F%3EON%20(%20NAME%20%3D%20testdb_dat%2C%20FILENAME%20%3D%20'https%3A%2F%2F%3CMYSTORAGEACCOUNTNAME%3E.blob.core.windows.net%2Ftest.mdf')%3CBR%20%2F%3ELOG%20ON%20(%20NAME%20%3D%20testdb_log%2C%20FILENAME%20%3D%20%E3%80%80%20%E2%80%98https%3A%2F%2F%3CMYSTORAGEACCOUNTNAME%3E.blob.core.windows.net%2Fdata%2Flog_test.ldf')%20%E3%80%80%3C%2FMYSTORAGEACCOUNTNAME%3E%3C%2FMYSTORAGEACCOUNTNAME%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EMsg%201802%2C%20Level%2016%2C%20State%204%2C%20Line%207%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3ECREATE%20DATABASE%20failed.%20Some%20file%20names%20listed%20could%20not%20be%20created.%20Check%20related%20errors.%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EMsg%205159%2C%20Level%2024%2C%20State%205%2C%20Line%207%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EOperating%20system%20error%20(null)%20on%20file%20%22https%3A%2F%2F%3CMYSTORAGEACCOUNTNAME%3E.blob.core.windows.net%2Ftest.mdf%22%20during%20MakePreviousWritesDurable.%3C%2FMYSTORAGEACCOUNTNAME%3E%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EMsg%20596%2C%20Level%2021%2C%20State%201%2C%20Line%206%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3ECannot%20continue%20the%20execution%20because%20the%20session%20is%20in%20the%20kill%20state.%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EMsg%200%2C%20Level%2020%2C%20State%200%2C%20Line%206%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3EA%20severe%20error%20occurred%20on%20the%20current%20command.%E3%80%80%20The%20results%2C%20if%20any%2C%20should%20be%20discarded.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20fix%20the%20error%20you%20have%20to%20enable%20trace%20flag%203979%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3Esudo%20%2Fopt%2Fmssql%2Fbin%2Fmssql-conf%20traceflag%203979%20on%3C%2FP%3E%0A%3CP%3ERestart%20the%20SQL%20Server%20for%20the%20changes%20to%20reflect.%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2030px%3B%22%3Esystemctl%20restart%20mssql-server.service%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20information%20about%20trace%20flag%203979%20can%20be%20found%20at%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4131496%2Fenable-forced-flush-mechanism-in-sql-server-2017-on-linux%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4131496%2Fenable-forced-flush-mechanism-in-sql-server-2017-on-linux%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1102576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3ECreating%20a%20database%20in%20Azure%20Blob%20Storage%20from%20SQL%20Server%20running%20on%20Linux%20OS%20may%20fail%20with%20Msg%201802%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Microsoft

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...