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
but as per the documentation, it cannot be executed on a FILESTREAM filegroup.
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.