December 21, 2011 at 5:16 am
OK I am trying to sell usage of DB Snapshot to my team. One big selling point is Size of Snapshot is less almost nothing compared to size of Original DB atleast as created for the 1st time and size is checked (assuming not much has changed in original DB). I run the following script
USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
GO
declare @i int
set @i = 0
while (@i < 100000)
begin
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
set @i = @i + 1
end
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
Much to my surprise and chagrin -- the Sanpshot Size is SAME as the RegularDB Database Size.
WHY? Are there are settings I am missing ?:crazy:
PS: This script has been copied from another blog, where they even show the DB sizes being different. But I am not seeing it.
I am using SQL Server 2008 R2.
Thanks
December 21, 2011 at 5:33 am
Locate the snapshot file on your file system, and loot at its properties. You'll see that the size is the same as your database file, but size on disk is a very small amount. This is due to the use of an NTFS feature called sparse file.
More information at Understanding Sparse File Sizes in Database Snapshots
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply