First published on MSDN on Jul 17, 2015
The history of sparse file support capabilities has lead to confusion that I would like to clear up.
Sparse files ARE SUPPORTED on NTFS and REFS.
SQL Server 2014
takes full advantage of the sparse support for online DBCC and database snapshots.
There are older blogs indicating limited support for sparse files on REFS that are outdated. REFS supports sparse files and does not encounter the limitation (665) as seen on NTFS deployments.
Confusion seems to stem from the limited support in SQL Server 2012. The REFS file system does not support large,
secondary streams
. Online DBCC uses a snapshot approach and prior to SQL Server 2014 placed the information in a secondary stream. Since the stream can’t support the SQL Server file sizes online DBCC could not be supported on REFS.
SQL Server 2014 creates a separate file, in the same location as the parent file, and marks it for sparse allocations when creating snapshot targets. This allows full support of Online DBCC and snapshot databases stored on REFS.
Another point of confusion is the reporting in Windows File Explorer. There are conditions where the file explorer indicates the same size on disk as allocated for sparse file. This is incorrect as the file on disk is often smaller than the target allocation.
Bob Dorr - Principal SQL Server Escalation Engineer
