I want to create DBSnapshot for reporting purposes of mirrored Databases of sizes 50GB and 100GB.
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
answered Apr 29, 2011 at 01:17 PM