Article ID: 2028447 - View products that this article applies to.
You notice that the database files of normal databases [not database snapshots] are marked as is_sparse = 1 in the catalog views sys.database_files and sys.master_files.
When you query the file attributes using fsutil sparse queryflag <database file name>, you get the result:
This file is set as sparse
You might also notice unexplained performance degradation when query data from these files.
The file system incorrectly marks these database files as sparse after an alternate stream is removed. Alternate streams are used by DBCC CHECKDB family of commands.
Depending upon the version of the SQL Server you are using, the steps to resolve this situation varies. Refer to the blog Did your backup program/utility leave your SQL Server running in an squirrely scenario?
Evaluate and apply the related fixes for the file system:
You cannot restore large files in the NTFS file system when all the data streams that have sparse attributes are deleted in the 64-bit version of Windows XP SP2 or in Windows Server 2003 SP2
Error message when you run the "chkdsk" command together with the "/v" switch on a Windows Server 2003-based computer: "Correcting sparse file record segment <number>"
FIX: Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 or in SQL Server 2008 even when the physical files are marked as not sparse in the file system
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Collapse this tableExpand this table
Article ID: 2028447 - Last Review: September 10, 2013 - Revision: 4.0