Why can a database snapshot run out of space?

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.

Discuss this Blog Entry 1

on Apr 11, 2011
FYI: there's a dmv exposing this info: (actual size vs os file size)
/*
DMV Database File Size on Disk.sql

*/
Select IVFS.database_id
, DB_NAME(IVFS.database_id) as DBName
, IVFS.file_id
, IVFS.size_on_disk_bytes /* Number of bytes used on the disk for this file.
For sparse files, this number is the actual number of bytes on the disk
that are used for database snapshots. */
, IVFS.size_on_disk_bytes / 1048576 as Effective_size_on_disk_MB
, MF.name as DbFileName
, MF.type_desc
, MF.[physical_name]
, MF.size * 8 / 1024 as FileSizeMB /*Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can use for the file. */

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
left join sys.master_files MF
on MF.database_id = IVFS.database_id
and MF.file_id = IVFS.file_id
order by DBName
, DbFileName
;
























Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×