July 8, 2011 at 8:53 am
Never having previously had the need for snapshotting I'm now in a situation where I need to support their use etc. And obviously I'm going to blame old age/losing the plot but can some one clarify ....
If I take a snapshot it is a copy of the database as of that moment. The snapshot is not then updated when I make subsequent changes to the source database.
So why then does it state on msdn etc "When a page getting updated on the source database is pushed to a snapshot".
surely if the snapshot is static then post creation changes to the source are not reflected in the snapshot ?
thanks simon
July 8, 2011 at 9:10 am
When you create the snapshot nothing happens (well, almost nothing). No data is copied to it.
When a change is made in the source database, the old version of the page (pre-change) is pushed to the snapshot if it's not already there. This way the snapshot appears to be a point-in-time copy of the DB when it could just contain a very small portion of that DB.
This is why a snapshot on a 5TB database can complete in milliseconds (no way you could copy 5TB in that time) and why it takes a very small amount of space initially.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2011 at 9:42 am
thank you very much Gail !
July 8, 2011 at 10:02 am
FYI:
/*
DMV Database File Size on Disk.sql
*/
Select IVFS.database_id
, DB_NAME(IVFS.database_id) as DBName
, IVFS.file_id
, IVFS.sample_ms /* Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function. */
, IVFS.num_of_reads /* Number of reads issued on the file */
, IVFS.num_of_bytes_read /* Total number of bytes read on this file. */
, IVFS.io_stall_read_ms /* Total time, in milliseconds, that the users waited for reads issued on the file. */
, IVFS.num_of_writes /* Number of writes made on this file. */
, IVFS.num_of_bytes_written /* Total number of bytes written to the file. */
, IVFS.io_stall_write_ms /* Total time, in milliseconds, that users waited for writes to be completed on the file. */
, IVFS.io_stall /* Total time, in milliseconds, that users waited for I/O to be completed on the file */
, IVFS.size_on_disk_bytes /* Number of bytes used on the disk for this file.
For sparse files, this number is the actual number of bytes on the disk
that are used for database snapshots. */
, IVFS.size_on_disk_bytes / 1048576 as Effective_size_on_disk_MB
, MF.name as DbFileName
, MF.type_desc
, MF.[physical_name]
, MF.size * 8 / 1024 as FileSizeMB /*Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file. */
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
left join sys.master_files MF
on MF.database_id = IVFS.database_id
and MF.file_id = IVFS.file_id
order by DBName
, DbFileName
;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply