IFI - instant file initialization?

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hi experts
1. When should I use IFI - instant file initialization?
2. Some URL that I can recommend for the implementation
3. Should I have it configured because it helps in the autogrowth?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
1. Unless you have security issues that prevent it, you should always use IFI

2. Not sure what you mean.  If you mean justification for using it, MS has some documentation on why it should be used except for security issues.

3. Yes.

Author

Commented:
1. What security problem could it have?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
IFI means the new disk space allocated is not writen over.  In theory, someone could look at the physical file and see data that was there from the previous file that used that disk space.  It's a rather obscure risk.  Unless you have credit card info or SSNs or some other really sensitive data that's not encrypted stored in a table, I wouldn't worry about it too much.

Author

Commented:
i do not understand
In theory, someone could look at the physical file and see data that was there from the previous file that used that disk space
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
First, IFI is applicable only for Data files and not for Log files.
Whenever IFI is enabled, SQL Server can expand the data files or create data files instantly without writing zeroes in the file system(you can assume it as formatting with zeroes so older files/folder can't be accessible).
>> 1. When should I use IFI - instant file initialization?
If you have huge databases with autogrowth value of data files say 4GB or more, then IFI can help expand the files faster.

>> 2. Some URL that I can recommend for the implementation
Some good URL's to start are..
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017
https://www.brentozar.com/blitz/instant-file-initialization/

>> 3. Should I have it configured because it helps in the autogrowth?

As mentioned earlier, if the database size is really huge or if the autogrowth values are really huge..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial