Disk space used by Snapshot - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

Disk space used by Snapshot

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 Shaw
SQL 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 me

Cheers,
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 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.

Read up on NTFS sparse files for the reasoning and the architecture of sparse files

--
Gail Shaw
SQL 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources