Filestream directory exists for a partition but the corresponding partition does not exist in the database

Question

I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs each with a FILESTREAM. As part of my investigation, I ran

DBCC CHECKDB ([db_name]) WITH ESTIMATEONLY;

to check if tempdb is sized correctly. The output was as follows.

DBCC results for 'db_name'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 53.
Msg 7933, Level 16, State 1, Line 1
Table error: A FILESTREAM directory ID b1e50dcc-0511-4488-8eeb-ea34966edfab exists for a partition, but the corresponding partition does not exist in the database.
Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 16379997.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I was surprised by the error considering that the senior DBA has all of Ola Hallengren's scripts running on a schedule. No errors were reported by these scheduled scripts and despite the error, the DBs are still functioning with the FILESTREAM.

I continued my troubleshooting on a test environment with a restore of the full production DB backups. As expected the same error is present. Consequently, I ran

DBCC CHECKDB ([db_name]) WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY;

but no errors were found. I wanted to execute

DBCC CHECKFILEGROUP

but as per the documentation, it cannot be executed on a FILESTREAM filegroup.

Question

Is the FILESTREAM-partition-does-not-exist-in-database-error a problem? Can it be resolved? It does not seem that any of the other DBCC commands detect a problem.

Answer 1

I know this question is old, but I've recently dealt with it, and opened a case with MS to troubleshoot the problem.

The goal was to turn off filestream in my database, but no amount of full/log backups, checkpoints, and forced garbage collections could get me past the error that the file could not be dropped because it was not empty. The issue was that in the filestream directory we still had these folders with many 0kb files in them. To get around this, the first step was to check and see if we had a partition scheme

select partition_scheme_id,destination_id,a.data_space_id,name,b.data_space_id
from sys.destination_data_spaces a
join sys.filegroups b
ON a.data_space_id = b.data_space_id

SELECT * FROM sys.partition_schemes
WHERE data_space_id = --ID from above query

DROP PARTITION SCHEME <partition_scheme_name>;

Once the partition was gone MS advised that I could go the the file system and safely delete the folders with the 0kb files in them. They advised that this would not cause the database to become corrupted, but that I should perform DBCC CHECKDB after dropping each database file.

This ultimately resolved my problem. The main issues were:

1. Understanding that the partition scheme existed and removing it before continuing
2. Understanding that the 0kb files on the filesystem were somehow orphaned and needed to be removed in order to drop the database file.

Hope this helps someone.