September 19, 2011 at 1:06 am
Hi,
I've read in the book "Exam 70-432: Microsoft SQL Server 2008—
Implementation and Maintenance" by Mike Hotek the following statement:
"When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent to the current size of the data files in the source database."
However, when I created a snapshot using the following command:
CREATE DATABASE [SDB_SNAP] ON PRIMARY
( NAME = N'SDB', FILENAME = N'D:\Microsoft SQL Server\SDB\SDB_PRIM_SNAP.MDF' ),
( NAME = N'SDATA', FILENAME = N'D:\Microsoft SQL Server\SDB\SDATA01_SNAP.NDF' )
AS SNAPSHOT OF SDB
I noticed the data files copied and were of the same sizes as the source database.
Is the statement wrong or am I misunderstood something?
September 19, 2011 at 2:59 am
The file will report the size the same as the original but if you check the space used (actual size on disk) you will see that it does not consume the space. It will consume space on disk as the original DB changes.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 19, 2011 at 3:18 am
but if you check the space used (actual size on disk) you will see that it does not consume the space
That's what I've already checked... as I said in my question, the snapshot data files (D:\Microsoft SQL Server\SDB\SDB_PRIM_SNAP.MDF' ,
and D:\Microsoft SQL Server\SDB\SDATA01_SNAP.NDF' ) consumed from disk space same size as in the source database.
September 19, 2011 at 3:35 am
I do not know why your system will do this. I retested and what happened on my side is as follows.
Without SNAP disk free space according to OS is 182Gb
After SNAP created the disk free space according to the OS is 182Gb
If I check the file on the disk it shows the same size as the original. If I check the file on the snap properties it also shows the same size but still as above the OS show same size open on the disk.
Hope this helps
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 19, 2011 at 4:33 am
If I check the file on the disk it shows the same size as the original.
do you mean the files of the snapshot take same size as the original?
If yes, then that's what I meant.
Regarding the free disk space reported by OS, it might be because the db size is not large enough to affect the calculated size.
Thanks for your effort.
September 19, 2011 at 4:39 am
I got the answer from another forum as follows:
"If you right-click a file in Explorer and chose properties, there are two sizes listed. Size and Size on disk
They're only different for sparse files (which is what snapshots are). For normal files those two will be the same. For sparse files, size is the apparent size of the file (and that will be the same as the DB), size on disk is the actual space that the file is taking and it will be lower than size."
Thanks all for your time.
September 19, 2011 at 2:21 pm
We use a sanpshot for a weekly CheckDB of a very large production database. We capture the snapshot size before and after the CheckDB process using this query. It might be helpful in your case.
select Cast(Cast(BytesOnDisk as decimal(19,3))/1024/1024 as decimal(19,3)) as BytesOnDiskMB, D.[name] as DBName, S.FileID, S.NumberReads, S.BytesRead,
S.IoStallWriteMS, S.BytesOnDisk, GetDate() AS CurrentTime, 'Before' as Stage
from fn_virtualfilestats(db_id('Test_DBCCSnap'),null) S
inner join
dbo.sysdatabases D
on D.dbid = S.dbid
September 19, 2011 at 5:54 pm
wow! amanzing query 🙂
I'm adding it to my notes.
Thanks for sharing.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply