Unable to remove FILESTREAM filegroup that is not in use

  • Adam Bean

    One Orange Chip

    Points: 26628

    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

     

  • Site Owners

    SSC Guru

    Points: 80376

    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: 22932

    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.

  • Adam Bean

    One Orange Chip

    Points: 26628

    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: 22932

    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.

  • Adam Bean

    One Orange Chip

    Points: 26628

    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: 22932

    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?

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

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