November 17, 2010 at 4:09 am
Hi All,
I have some requirement to create new database snapshot on my production database per day at 00 hours. And I have planned to drop old snapshots/rotate it of regular basis.
My problem is that when I create a snapshot the sparse file size will reach the exact as source data file size.
Let’s say my source database’s data file size on disk is 50GB (because I have pre-allocate the disk space instead of autogrowth) and actual used space 10GB in that file. Now I creates database snapshot on this data file my snapshot’s sparse file (.ss) size also the exact 50GB.
Due to it the Database Snapshot feature is not helping in my case. It consuming lots of Disk space. I query on internet and MSDN but did not found the way to shrink sparse file or how to achieve my requirement.
Does anyone have any idea?
Ram
MSSQL DBA
November 17, 2010 at 4:26 am
The size of the sparse file may be 50GB, but the size on disk should not be. Sparse files are only as large as the data in them. If you've changed 5GB of data since creating the snapshot, it should be occupying no more than 5 GB.
Check the properties of the file (right click - properties) and look at the size on disk.
As for shrinking, not possible. Snapshots are readonly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2010 at 5:51 am
Hi Gail Shaw,
You are right! When I right click on .ss file and check file property it shows File Size: 50GB and Size on disk: 200MB That’s I am looking for.
Hunn! It was my mistake; I did not check that before submit this question on sqlservercentral.com
Thank you very much!
Ram
MSSQL DBA
November 17, 2010 at 8:22 am
Reo (11/17/2010)
You are right! When I right click on .ss file and check file property it shows File Size: 50GB and Size on disk: 200MB That’s I am looking for.
That's what I expected. The size will always be the same size as the data file it's a snapshot of. The size on disk will be the size of the data inside it and that's how much space it actually takes on disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply