Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ozSQLServer
Starting Member
32 Posts |
Posted - 2011-09-19 : 03:06:08
|
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?Cheers,ozSQL |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-19 : 05:03:15
|
There's a difference between the size (as seen in Explorer) which will show as the same size as the DB, and the size on disk (right-click->properties) which is the actual space that the file takes on disk. The size on disk is what that statement refers to.--Gail ShawSQL Server MVP |
 |
|
ozSQLServer
Starting Member
32 Posts |
Posted - 2011-09-19 : 05:22:26
|
Thanks for your reply.Just for clarifiaction, you said: "the size on disk (right-click->properties) which is the actual space that the file takes on disk"I cannot understand the different between the "size as shown by explorer" and the "actual space that the file takes on disk" sorry,, they the two expressions look the same to meCheers,ozSQL |
 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-19 : 05:53:37
|
If you right-click a file in Explorer and chose properties, there are two sizes listed. Size and Size on diskThey'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.Read up on NTFS sparse files for the reasoning and the architecture of sparse files--Gail ShawSQL Server MVP |
 |
|
ozSQLServer
Starting Member
32 Posts |
Posted - 2011-09-19 : 06:37:19
|
Hi Gail,Thanks a lot for taking time to explain the issue. That was fantastic and quite clear.Have a good day!Cheers,ozSQL |
 |
|
|
|
|
|
|