2

Database snapshot files are sparse files. Only when a page is modified the sparse file is being written to. Reads of the snapshot must be able to know whether the sparse file contains a copied page or not. How does SQL Server know where to read from? Where is this information stored?

3

From the Microsoft SQL Server 2012 Internals book:

The snapshot files contain only the data that has changed from the source. For every file, SQL Server creates a bitmap that is kept in cache, with a bit for each page of the file, indicating whether that page has been copied to the snapshot. Every time a page in the source is updated, SQL Server checks the bitmap for the file to see if the page has already been copied and if hasn't it is copied at that time. This operation is called a copy-on-write operation.

The bitmap is stored in cache, not the file itself, so it is always readily available. When SQL Server shuts down or the database is closed, the bitmaps are lost and need to be reconstructed at database startup.

When performing reads, SQL Server checks the snapshot bitmap to determine where to read from.

  • 1
    Thanks! I failed to find this particular source. It looks like on startup the bitmap is reconstructed by querying NTFS (there is a Windows API for that). The bitmap is surprisingly small. 10TB file = 160MB bitmap (10*2^40/8192/8/2^20). – boot4life Oct 20 '15 at 16:12
2

Background from the documentation:

Source database

For a database snapshot, the database on which the snapshot was created. Database snapshots are dependent on the source database. The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.

Sparse file

A file provided by the NTFS file system that requires much less disk space than would otherwise be needed. A sparse file is used to store pages copied to a database snapshot. When first created, a sparse file takes up little disk space. As data is written to a database snapshot, NTFS allocates disk space gradually to the corresponding sparse file.

Location

Specify the default snapshot location on the Snapshot Folder page of the Configure Distribution Wizard. If you create a publication on a server that is not configured as a Distributor, specify a default snapshot location on the Snapshot Folder page of the New Publication Wizard.

To modify the default snapshot location

  • On the Publishers page of the Distributor Properties - dialog box, click the properties button (…) for the Publisher for which you want to change the default snapshot location.
  • In the Publisher Properties - dialog box, enter a value for the Default Snapshot Folder property.
  • 1
    Did you simply copy and paste material from the official documentation? – Aaron Bertrand Oct 20 '15 at 14:57
  • First two paragraphs are from official documentation to make an intro. I didn't want to post the link and write read this. Last part is what you wanted to know, and someone who is reading this maybe doesn't know the terminology of sparse file and source database as snapshots concerned. If that's a problem for you I'll edit the post and just leave the last paragraph. – Josip Ivic Oct 20 '15 at 15:00
  • 1
    It's not a problem, you should just attribute the source of text if it came from elsewhere. I'll show you how. – Aaron Bertrand Oct 20 '15 at 15:01
  • How does SQL Server know whether it needs to copy a page it wants to write to or not? I can't tell from the text quoted here. – boot4life Oct 20 '15 at 16:10
0

This information about the publisher and distributor screens seems very wrong, as if you have to replicate a db in order to use db snapshots. It looks like this entry is conflating replication snapshot files with database snapshots.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.