Modify Size for datafile clarification

Question

Modify Size for datafile clarification

jjturner

Right there with Babe

Points: 788
More actions
March 25, 2014 at 4:18 pm

#284928

From BOL, I see these remarks with respect to the MODIFY FILE subcommand (my underline added):
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
Create a database
Add files to an existing database
Increase the size of an existing file
Restore a database or filegroup
Which leads me to believe that expanding the size of a datafile will also wipe out (my definition of 'initialize') any existing data within that file.
I may be misunderstanding 'initialize', because when I tested it out, I found this wasn't the case - my table data written to the file was still there after a resize.
So I'd be much obliged if anyone can clarify to what degree I'd be taking a risk by increasing the file size on a datafile which already has data in it.
Thanks,
John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

No it will not wipe out existing data. But fill the extended files with 0. i.e. if you do have file size 100 MB with data of 80 MB and expanding by 50 MB it will fill 50 MB with 0.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

jjturner Right there with Babe Points: 788 More actions · Answer 2

Thanks for the verification, that puts my worries to rest -

Now I’m guessing that with Instant File Initialization turned on, it may simply be allocating the new space rather than going the extra step of 0-filling. Time to go back and test that one..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

jjturner (3/26/2014)
Now I’m guessing that with Instant File Initialization turned on, it may simply be allocating the new space rather than going the extra step of 0-filling. Time to go back and test that one..

Correct, providing it's a data file. Log files are always zdero-initiaised.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass