Question: I’ve been using database snapshots since SQL Server 2005 was released but recently I’ve been having problems where the snapshot becomes unavailable because there isn’t enough space. I’ve read that a database snapshot reserves space when it is created, so how can it run out of space?
Answer: Unfortunately what you’ve read is incorrect. A database snapshot does not reserve space when it is created so it is quite possible for it to run out of space (and hence become unusable).
Database snapshots use NTFS sparse files—you can have an arbitrarily large file that takes up very minimal space on disk. For instance, a 100GB sparse file that contains 2MB of data at offset zero in the file, and 3MB of data at offset 64MB in the file will only occupy 5MB of disk space instead of 100GB. NTFS keeps track of which offsets in the file contain data and stores all the data in a compacted form so that minimal space is required. This is the same concept as sparse arrays in programming languages.
Very simply, when a database snapshot is first created, it will be very small. As pages in the source database (the database on which the database snapshot was created) are about to change, they are pushed into the database snapshot, which has to expand to accommodate the new data. Eventually if all the pages in the source database change then the database snapshot may become the same size as the source database.
If at any point there is not enough space on the disk volume holding the sparse file to allow the sparse file to expand, then a write to the sparse file will fail, the database snapshot becomes inaccessible, and you’ll see the following error:
Msg 5128, Level 17, State 2, Line 1
Write to sparse file 'C:\SQLskills\BigDatabase_SS.NDF_SS' failed due to lack of disk space.
However, the query that caused the writes to the database snapshot will not fail.
Additionally, you’ll see messages in the error log:
2011-04-08 14:32:12.26 spid59 Error: 17053, Severity: 16, State: 1.
2011-04-08 14:32:12.26 spid59 C:\SQLskills\BigDatabase_SS.NDF_SS: Operating system error 112(There is not enough space on the disk.) encountered.
2011-04-08 14:32:12.27 spid59 Error: 3420, Severity: 21, State: 1.
2011-04-08 14:32:12.27 spid59 Database snapshot 'BigDatabase_SS' has failed an IO operation and is marked suspect. It must be dropped and recreated.
Note that even if a database snapshot goes suspect, this does not affect the source database. If this occurs, the only thing you can do is to drop and recreate the database snapshot—there is no way to recover from a database snapshot going suspect.
This can also happen to the database snapshot that is automatically created when running DBCC CHECKDB (or any of the other DBCC CHECK* commands), in which case the DBCC command will fail. You can work around this by creating your own database snapshot on a volume with sufficient disk space and then running DBCC CHECKDB on that snapshot.
