You are currently offline, waiting for your internet to reconnect

SQL Server database files are incorrectly marked with the sparse attribute

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.
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:

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: 09/10/2013 18:47:00 - Revision: 4.0

  • 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
  • KB2028447
Feedback