What are Sparse Files and Why Should I Care as SQL Server DBA?
Last week I worked with one of my co-workers from the Windows Platform Support group in a backup-related issue with SQL Server data files. We both learn several things because of that case and I think it is worth the time to share this information with you.
Our customer was using Microsoft Data Protection Manager to backup his Windows servers and SQL Server databases and was finding several errors when copying the SQL Server databases. During our research, the customer found the following MSND post with a detailed explanation of the problem: “Did your backup program/utility leave your SQL Server running in an squirrely scenario?” If you have never heard of sparse files and how they affect SQL Server I highly recommend you to read this post from the CSS SQL Server Engineers’ blog.
What are sparse files?
Sparse files are created during SQL Server operations such as database snapshots or DBCC CHECKDB statements. In the case of a snapshot, the sparse file is deleted when the snapshot is deleted while in the case of a DBCC CHECKDB operation the sparse file is automatically deleted by SQL Server right after the execution. You can find extended information for the role of sparse files in SQL Server in the two following links:
DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed. For more information, see Understanding Sparse File Sizes in Database Snapshots and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL).
Understanding Sparse File Sizes in Database Snapshots,
Sparse files are a feature of the NTFS file system. Initially, a sparse file contains no user data, and disk space for user data has not been allocated to it. For general information about the use of sparse files in database snapshots and how database snapshots grow, see How Database Snapshots Work.
When first created, a sparse file takes up little disk space. As data is written to the sparse file, NTFS allocates disk space gradually. Potentially, a sparse file can grow very large. If a database snapshot runs out of space, it is marked as suspect, and it must be dropped. The source database, however, is not affected; actions on it continue normally.
How this happens?
Sparse files is a feature provided by the NTFS file system. Under some NTFS.SYS file versions the sparse file bit becomes sticky for the main (parent) file. Here is one example:
- You run a DBCC CHECKDB on your SQL Server database as part of your maintenance plan, this completes with no errors
- You perform a backup of your databases
- Your original database files are then marked as ‘sparse’
What is the problem?
SQL Server does not support backup over sparse files so you can put yourself in an unsupported scenario without even knowing it.
How can I check if I am in this situation?
Run the following query and look for values other than 0 (zero) under the ‘is_sparse’ column:
use <DatabaseName>
SELECT is_sparse, * from sys.database_files
The following example shows a database with several sparse files:
SQL Server 2008 R2 Best Practices Analyzer includes a new rule to detect this condition too, you can find this information in the Microsoft KB2028447 article.
What can I do to avoid the problem from occurring?
You should update the NTFS.SYS version in your Windows Servers as a first step. The latest NTFS.SYS versions prevent this problem from happening. Here is a list of the hotfixes and NTFS.SYS versions you should keep in mind:
For Windows Server 2003:
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, https://support.microsoft.com/kb/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> , https://support.microsoft.com/default.aspx?scid=kb;en-us;932021
For Windows Server 2008:
Disk Manager reports incorrect disk usage a while after the operating system starts in Windows Server 2008 or in Windows Vista, https://support.microsoft.com/kb/981891
For SQL Server 2005 and 2008:
In addition to the abovementioned Windows hotfixes, make sure you have the latest SQL Server Service Packs and Cumulative Updates installed in both platforms to prevent this issue from happening.
How can I fix the problem?
Having your Windows and SQL Server boxes updated with the latest service packs and hotfixes will prevent the problem from happening again, but will not fix the problem if it already exists on the database. The previously mentioned MSDN blog post explains how to fix this problem in SQL Server 2005 and 2008: “Did your backup program/utility leave your SQL Server running in an squirrely scenario?”
Another option you can use to fix this problem is to empty the content of the database file to another file in the same filegroup. You can achieve this by using the EMPTYFILE option in the SHRINKFILE T-SQL statement; here is an example:
USE AdventureWorks2008R2;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2
ADD FILE (
NAME = Test1data,
FILENAME = 'C:\t1data.ndf',
SIZE = 5MB
);
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE Test1data;
GO
This method can not be used if the file with the ‘sparse’ sticky bit is the first file on the PRIMARY filegroup because this file contains several system objects that cannot be moved.
After following all these steps I still find my SQL Server data files as ‘sparse’!
After following these steps you may find that Windows fsutil reports the file as ‘no sparse’ while SQL Server keeps reporting the file as ‘sparse’ via sys.database_files. If this is the case, make sure you detach and re-attach the database files; this is needed because the sys.database_files information has to be updated.