SQL Server Snapshot Sparse File Size

When you execute the CREATE AS SNAPSHOT sql statement, the snapshot creates a NTFS sparse file. In a sparse file only “nonzero” information is stored on the disk. The maximum size of a database snapshot is the size of the source database at snapshot creation. In Windows Explorer, the snapshot files are shown with a size matching that of the source database. However, when you go into the file properties you will see that the size on disk is 0KB or a multiple of 64KB (representing 1-8 data pages) if some data has been transferred.

At the beginning, there is no source/user data in the snapshot file. It’s only after changes are made in the source database that data pages get written to the snapshot file. When a data page in the source database is changed, the original data page is first copied to the snapshot file before updating in the source database (copy-on-write). The first time any page is written to the sparse file the entire 64K extent is allocated. All other pages in the extent are written as zero's.

When a sparse file is populated, Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented.

• Windows 2003 - 64GB (Error 1450 returned)
• Windows 2008 and Vista - 16GB (Error 665 returned)

Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

Windows 2008 Error: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8'

Let’s take a look at what takes place when the snapshots are created.

• A sparse file is created. This file will have the same file size as the database. Remember, the only data that may be contained in the snapshot file at this point are pointers to the source database data files. Only after something changes in the source database will copy-on-write insert data into the snapshot file.
  
• The database is checkpointed (all dirty pages in the buffer cache are written to disk).
  
• If uncommitted transactions are in progress in the database, the snapshot will reflect the state of the database before these transactions started in other words, in the context of the snapshot, these transactions will be rolled back – but transactions in the database itself are not.
  
• The snapshot is brought online and is ready for use.
  

Quick Note:

From a Disaster Recovery standpoint, SQL Server snapshots are not very useful as they require an online database to function. In situations where there is a hardware failure, the database is no longer online and the snapshot becomes useless. Also, snapshots cannot be restored when users are accessing the database. So if there is a roll back in progress or some other process that does not allow the database’s state to be changed, snapshot restores can not be applied. However, in situations where an object was accidently deleted or altered, and needs to be restored, snapshots are ideal.

For a more detailed look inside how Copy-On-Write works, see: http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx