How much time will it take to create a Database snapshot

I want to create DBSnapshot for reporting purposes of mirrored Databases of sizes 50GB and 100GB.
How much time will it take to create a Database snapshot of a Database with 50GB/100GB size(Assuming the users might update almost all the data(pages) in Principal Database which is going to reflect on mirrored DB).
How frequently can i create them without any performance issues?


more ▼

asked Apr 29, 2011 at 12:56 PM in Default

avatar image

535 41 42 47

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I really can't give you an answer about how long it will take to create a snapshot of a large database. But I imagine it won't take long at all, since taking a snapshot doesn't really copy any data from the source. It's when a data page in the source database is changed that the original page is copied to the snapshot before the data page is modified in the source database. So I'd say creating a database snapshot of a 100GB source database is done in seconds rather than minutes.

You should see the snapshot as an empty file. It fills up as the source database is changed. Therefore you might get a performance hit when doing the first update to a page in the source database (since the page must be copied to the snapshot file before changes are written). If you have multiple snapshots, you copy data to multiple files prior to data being written to disk.

Conceptual overview of database snapshots: http://msdn.microsoft.com/en-us/library/ms187054.aspx

Sparse files (which is what snapshots use): http://msdn.microsoft.com/en-us/library/ms175823.aspx

For a more in depth discussion about performance and snapshots: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBSnapshotPerf.docx

more ▼

answered Apr 29, 2011 at 01:17 PM

avatar image

Magnus Ahlkvist
20.9k 19 38 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 29, 2011 at 12:56 PM

Seen: 1749 times

Last Updated: Apr 29, 2011 at 01:30 PM

Copyright 2016 Redgate Software. Privacy Policy