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).
- 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!
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
select t.name as table_name,
i.name as index_name,
ds.name as data_space_name,
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'