SQL Server database files are incorrectly marked with the sparse attribute

Article translations Article translations
Article ID: 2028447 - View products that this article applies to.
Expand all | Collapse all

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.

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.

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?

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


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 softwareRule titleRule descriptionProduct 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 






Properties

Article ID: 2028447 - Last Review: September 10, 2013 - Revision: 4.0
Applies to
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2028447

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com