Understanding Sparse File Sizes in Database Snapshots
A database snapshot uses one or more sparse files to store data. During database snapshot creation, sparse files are created by using the file names in the CREATE DATABASE statement. These file names are stored in sys.master_files in the physical_name column.
Note
In sys.database_files (whether in the source database or in a snapshot), the physical_name column always contains the names of the source database files.
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.
Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB. The latest 64-KB increment holds from one to eight 8-KB pages, depending on how many pages have been copied from the source database. This means that, on the average, the size of a sparse file slightly exceeds the space actually filled by pages.
- To find out the actual size of a sparse file
To learn the number of bytes each sparse file of the snapshot is currently using on disk, you can use the fn_virtualfilestats system table-valued function, which returns the current number of bytes in a file in the BytesOnDisk column. This function takes database_id and file_id as values. The database ID of the snapshot and the file IDs of each of its sparse files are displayed in the sys.master_files catalog view. For more information, see sys.master_files (Transact-SQL) and fn_virtualfilestats (Transact-SQL).
Alternatively, to see the disk space used by a sparse file, you can right-click the file in Microsoft Windows, click Properties, and look at the Size on disk value. - To find out the maximum size of a sparse file
The maximum size to which a sparse can grow is the size of the corresponding source database file at the time of the snapshot creation. To learn this size, you can use one of the following alternatives:- Use Windows dir commands.
- Select the sparse file, open the file Properties dialog box in Windows, and look at the Size value.
- Select the size column from either sys.database_files in the database snapshot or from sys.master_files. The size column in sys.database_files or sys.master_files reflects the maximum space, in SQL pages, that the snapshot can ever use; this value is equivalent to the Windows Size field, except that it is represented in terms of the number of SQL pages in the file; the size in bytes is:
( number_of_pages * 8192)
See Also
Concepts
Database Snapshots
How Database Snapshots Work
Other Resources
fn_virtualfilestats (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)