x

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?

Thanks.....
more ▼

asked Apr 29 '11 at 12:56 PM in Default

srivivek gravatar image

srivivek
519 39 42 43

(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][3]

[3]: http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/DBSnapshotPerf.docx
more ▼

answered Apr 29 '11 at 01:17 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x13

asked: Apr 29 '11 at 12:56 PM

Seen: 1214 times

Last Updated: Apr 29 '11 at 01:30 PM