Database Snapshot (SQL Server 2005)

11 Nov 2005 11:59 AM

Leila

Hi,
-------
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
-------
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!

Any help would be greatly appreciated.
Leila

11 Nov 2005 1:32 PM

Tibor Karaszi

Right-click the file in explorer, properties, check "size on disk".

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Leila" <Lei***@hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@TK2MSFTNGP10.phx.gbl...
> Hi,
> -------
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> -------
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
>
> Any help would be greatly appreciated.
> Leila
>

11 Nov 2005 1:33 PM

Andrew J. Kelly

That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.

--
Andrew J. Kelly SQL MVP

Show quote

"Leila" <Lei***@hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@TK2MSFTNGP10.phx.gbl...
> Hi,
> -------
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> -------
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
> Any help would be greatly appreciated.
> Leila
>

11 Nov 2005 1:49 PM

David Browne

"Leila" <Lei***@hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@TK2MSFTNGP10.phx.gbl...
> Hi,
> -------
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> -------

I hope not. It's just SQL Server.

> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>

Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.

http://msdn.microsoft.com/en-us/library/ms175823.aspx

Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.

David

11 Nov 2005 9:59 PM

Jasper Smith

And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.

select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)

You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

Show quote

"Leila" <Lei***@hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@TK2MSFTNGP10.phx.gbl...
> Hi,
> -------
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> -------
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
> Any help would be greatly appreciated.
> Leila
>