0

I am trying to create a database with FILESTREAM attachments but getting a strange error when doing so.

Msg 5120, Level 16, State 106, Line 14

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL15.MYINSTANCE\MSSQL\DATA\pitti3w5.r2s.attachments.ndf". Operating system error -1073741496: "0xc0000148(failed to retrieve text for this error. Reason 317)".

Here is the SQL code to create the DB:

USE [master]

CREATE DATABASE 
[MyDBTest] ON (NAME = 'Data', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MYINSTANCE\MSSQL\DATA\pitti3w5.r2s.data.mdf')
LOG   ON (NAME = 'Log',    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MYINSTANCE\MSSQL\DATA\pitti3w5.r2s.log.ldf')


-- ----------------------------------------------------------------------------------------------------
-- Create a Filegroup for storing attachments added by the user
-- ----------------------------------------------------------------------------------------------------
ALTER DATABASE [MyDBTest] ADD FILEGROUP AttachmentFiles CONTAINS FILESTREAM
ALTER DATABASE [MyDBTest] ADD FILE(NAME = 'Attachments', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MYINSTANCE\MSSQL\DATA\pitti3w5.r2s.attachments.ndf') TO FILEGROUP [AttachmentFiles]

This only throws the error if I include the keywords CONTAINS FILESTREAM. For some reason it works fine if I remove them (though now I don't have the required filestream enabled attachment files so this is not a solution). But it even creates the files in the same location, so it is not a permissions issue or an issue with the path not existing.

I've tried removing SQL Server and reinstalling it, and tried with several different versions of it (2014, 2019, 2012 Developer Edition)

I also didn't have the problem some time ago, it was only when I had to remove a previous installation of SQL Server (2014) and install 2019 that this issue first occurred (although now, going back to 2014 doesn't fix it).

It appears to be related to my installation of Windows (10) or SQL Server, because if I run the same query on another machine (also Windows 10 with SQL 2019), including CONTAINS FILESTREAM it works fine.

I can't seem to find much on this error 0xC0000148 online though.

What could be the problem?

komodosp
  • 3,316
  • 2
  • 30
  • 59
  • "For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement." So does the folder `C:\Program Files\Microsoft SQL Server\MSSQL15.MYINSTANCE\MSSQL\DATA\pitti3w5.r2s.attachments.ndf` *not* exist? The error number is `-1073741496` which is `C0000148` in hex, so probably is this issue. – Charlieface Aug 02 '22 at 15:13
  • @Charlieface - The .ndf folder doesn't already exist. Also, the error I get if it does already exist is "Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SPIROTRAC\MSSQL\DATA\pitti3w5.r2s.attachments.ndf' because it already exists" – komodosp Aug 02 '22 at 16:21
  • Double check that the SQL Server service account has the `Create folders / append data` permission on the parent folder, and also try doing the obvious: `chkdsk c: /scan` and `dism /Online /Cleanup-Image /RestoreHealth` and `sfc /scannow` and check you have the latest build of Windows and SQL Server. – Charlieface Aug 02 '22 at 17:00

0 Answers0