Unable to remove FILESTREAM filegroup that is not in use

Question

Post reply

Unable to remove FILESTREAM filegroup that is not in use

Adam Bean

One Orange Chip

Points: 26631

June 26, 2020 at 7:10 pm

#3765269

Hey all,

Know very little about FILESTREAM, but attempting to remove it on a copy of a database as I'm troubleshooting some odd performance issues (excessive memory_allocation_ext wait types) and want to rule out FILESTREAM (unlikely a culprit, but found articles referencing it).

ANYWAY ...

There are no objects associated with this file
No partitions
0kb file size
Switched to simple recovery
Tried DBCC SHRINKFILE, EMPTYFILE
Tried SP_FILESTREAM_FORCE_GARBAGE_COLLECTION

All of the above still yell at me saying the file isn't empty!

Thoughts?

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id
AND i.data_space_id = 2 -- filestream filegroup

No data

select t.name as table_name,
    i.name as index_name,
    ds.name as data_space_name,
    ds.type_desc,
    ps.name as partition_scheme_name
from sys.tables t
join sys.indexes i on t.object_id = i.object_id
join sys.data_spaces ds on i.data_space_id = ds.data_space_id
left join sys.partition_schemes ps on ps.data_space_id = ds.data_space_id
where ds.name = 'fs_FACES'

No Data

Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Do you use FILESTREAM at all? If not, it may be safe to just turn the option off at the instance level or configure it to be "blank" at the database level.

I am pretty sure you cannot drop a filestream filegroup as it is a physical folder location on disk.

TSQL to turn it off if you prefer that over the GUI:

EXEC sp_configure 'filestream access level',0
reconfigure

Something to note - this is an INSTANCE level setting, so turning it off will turn it off for all of your databases.

If memory serves, FILESTREAM space is the space used by the folder on disk.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Thanks Brian Gale. I was going to reserve that as a follow up option. Would like to figure out how to turn it off on a replica of the database first. I am still striking out.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

Ok, was reading through this blog:

https://www.mssqltips.com/sqlservertip/5858/how-to-remove-filestream-from-a-sql-server-database/

and my understanding of filestream was off. But came up with a thought - Do you get any results when you run this query:

SELECT * FROM [sys].[tables]
WHERE [filestream_data_space_id]IS NOT null

Got this thought from the blog because of this line:

ALTER TABLE dbo.MyFSTable SET (FILESTREAM_ON="NULL") 
GO

My thought here is that MAYBE SQL is thinking that you have a table with filestream access on even though you told it you don't.

Failing that, does anything in the blog help? Like, for example, they needed to run garbage collection 3 times for it to work properly for them to shut off filestream.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Well damnit, yep ... there is a table there. I thought I checked this before. Found a single table, no data in it.

Using

ALTER TABLE dbo.blob SET (FILESTREAM_ON="NULL")

Results in "Cannot drop FILESTREAM filegroup or partition scheme since table 'BLOB' has FILESTREAM columns."

Dropped the column, dropped file, dropped file group
No filestream_data_space_id's of 2 exist now.

So, now the query in question, went from waiting on memory_allocation_ext to SOS_SCHEDULER_YIELD. Progress!

Thanks!

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 6

Glad you tackled one problem. Too bad it created a new one.

If memory serves, SOS_SCHEDULER_YIELD is related to CPU utilization and may be safe to ignore. A good writeup on this:

https://www.sqlshack.com/how-to-handle-excessive-sos_scheduler_yield-wait-type-values-in-sql-server/#:~:text=The%20SQL%20Server%20SOS_SCHEDULER_YIELD%20is%20a%20fairly%20common,working%20efficiently%20There%20is%20a%20pressure%20on%20CPU

My guess is that query is heavy on the CPU?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.