CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012filestream
user145610
user145610

Reputation: 3035

Unable to delete SQL File table

We are facing issue in removing SQL Filetable. Whenever I run 

select * from <MY SQL FILE TABLE>

I get this error message

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

When I see sp_readerrorlog, I see this message

SQL Server internal error. FILESTREAM manager cannot continue with current command.

When I run this query 

ALTER DATABASE XXX
REMOVE FILEGROUP FStream1

I get this message:

The filegroup 'FStream1' cannot be removed because it is not empty.

I tried to delete the SQL Filetable 

drop table `SQLFiletable_bck`

but I get this error :

The FILESTREAM filegroup 'FileStreamGroup1' has no files assigned to it. FILESTREAM data cannot be populated on this filegroup until a file is added.

Then I tried this option 

ALTER TABLE SQLFiletable_bck 
SET (FILESTREAM_ON = "NULL")

but I get this error

Cannot drop FILESTREAM filegroup or partition scheme since table 'SQLFiletable_bck ' has FILESTREAM columns.

I was not able to drop the SQL File table

Can anyone tell me any way to forcefully cleanup FileStream and its FileGroup in SQL Server 2012?

Upvotes: 2

Views: 2079

Answers (1)

simaglei
simaglei

Reputation: 1248

In order to fix

ALTER TABLE TableName SET (FILESTREAM_ON = "NULL")

you have to drop the column(s) using FILESTREAM. In order to be able to do that, you probably have to drop some constraint, too:

ALTER TABLE TableName DROP CONSTRAINT DF_TableName_ColumnName;
ALTER TABLE TableName DROP COLUMN ColumnName;
ALTER TABLE TableName SET (filestream_on = "NULL");
ALTER DATABASE DatabaseName REMOVE FILE 'DatabaseFileName');
ALTER DATABASE DatabaseName REMOVE FILEGROUP "FilegroupName";

Upvotes: 1

Related Questions