I have an issue with one of my SQL Server 2014 databases. It appears to me that the Filestream size is significantly larger than the actual Filestream container folder size? ~360Mb larger.
The following query give me the location and size of the Filestream objects:
SELECT physical_name, (Size * 8 / 1024) AS SizeMb
FROM sys.database_files WHERE type = 2
C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\FilestreamDB_FS
867418
Yet if I go to file explorer right click and properties to view the size of the folder it is 502Gb:
These figures used to match until 2 weeks ago. Why the disparity?
I am not convinced that it is compression. The reason is that until about 2 weeks ago Filestream was growing slowly and was only ~490Gb but over the weekend of the 30th June the size suddenly jumped (see attached image) and no longer matches the folder size - which it used to. The only things running at the weekend would be maintenance jobs, such as index rebuild etc.
I have read How It Works: FileStream (RsFx) Garbage Collection and have tried to force the garbage collector to run; however, it did not free up any extra space. This database is in simple recovery mode.
Additional information
Database Size:
Database Size History:
