Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Disadvantages of DB Snapshots

DB snapshots can grow in size very fast, depending on how fast data is updated or deleted in your source database. Note that inserted data may also increase the snapshot size, because the new rows may be added to existing pages, which will require the original pages to be copied to the snapshot. So if you have many snapshots, disk space can be an issue.

Also, when updated or deleted pages are copied to the snapshot, CPU and disk overhead is a factor. This will slow down operations against the source database. Again, if you have many snapshots, you need to watch out for this potential problem.

There are many restrictions on the source database if it has snapshots created on it. For example, you cannot drop the database or drop files from the source database. You have to drop all snapshots first. You cannot detach/attach the source database when it has snapshots. You can add data files, but not drop them.

If you’re using the snapshot for reporting purposes, be aware that queries to the snapshot will take longer, because the engine needs to collect the data pages from its local files and/or source database files. Also, you cannot add any indexes to the snapshot, since it’s read-only. Thus, it’s impossible to optimize data access for your reporting queries.

  • + Share This
  • 🔖 Save To Your Account