Select the product you need help with

SQL Server database files are incorrectly marked with the sparse attribute

Article ID: 2028447 - View products that this article applies to.

SYMPTOMS

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.

Back to the top | Give Feedback

CAUSE

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.

Back to the top | Give Feedback

RESOLUTION

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?

(http://blogs.msdn.com/psssql/archive/2009/09/23/did-your-backup-program-utility-leave-your-sql-server-running-in-an-squirrely-scenario.aspx)

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

(http://support.microsoft.com/default.aspx?scid=kb;EN-US;973886)

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>"

(http://support.microsoft.com/default.aspx?scid=kb;EN-US;932021)

Back to the top | Give Feedback

MORE INFORMATION

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

Rule software	Rule title	Rule description	Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)	SQL Database file may have sparse attribute set incorrectly	The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where the database files can have their attribute set to sparse incorrectly. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. If you run the BPA tool and encounter an Error with the title of Database Engine -SQL Database file may have sparse attribute set incorrectly, then you need to verify the number of Virtual Log Files for the affected database and reconfigure the transaction log files.	SQL Server 2008 SQL Server 2008 R2
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)	SQL Database file may have sparse attribute set incorrectly	The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where the database files can have their attribute set to sparse incorrectly. If you run the BPA tool and encounter an Error with the title of Database Engine -SQL Database file may have sparse attribute set incorrectly, then you need to verify the number of Virtual Log Files for the affected database and reconfigure the transaction log files.	SQL Server 2012