Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Modify Size for datafile clarification Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 4:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:33 AM
Points: 235, Visits: 319
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
Post #1554715
Posted Tuesday, March 25, 2014 11:28 PM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1554775
Posted Wednesday, March 26, 2014 6:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:33 AM
Points: 235, Visits: 319
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..
Post #1554902
Posted Wednesday, March 26, 2014 6:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1554909
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse