Instant File Initialization

Question

Instant File Initialization

zi

SSCrazy

Points: 2387
More actions
January 4, 2013 at 8:23 am

#268909

Dear friend
I have read and applied the instant file initialization but there is no difference?
first : I deleted the database and restored it -- it tooks 5 minites
second: I appplied local security pliocy -- security settings -- user rights assignments -- perform volume maintenance tasks. I double click on it and add sql users and groups , then reboot the device.
third : I delete the database then restored it expecting a difference or shrinking restoration time. but the result was same.
the question is why there is no differences after these changes.
the souces of knowldge is : http://timradney.com/2012/05/30/instant-file-initialization-for-sql-server-performance/
Thank you very much

Viewing 10 posts - 1 through 9 (of 9 total)

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

zi (1/4/2013)
Dear friend
I have read and applied the instant file initialization but there is no difference?
first : I deleted the database and restored it -- it tooks 5 minites
second: I appplied local security pliocy -- security settings -- user rights assignments -- perform volume maintenance tasks. I double click on it and add sql users and groups , then reboot the device.
third : I delete the database then restored it expecting a difference or shrinking restoration time. but the result was same.
the question is why there is no differences after these changes.
the souces of knowldge is : http://timradney.com/2012/05/30/instant-file-initialization-for-sql-server-performance/
Thank you very much

During a restore the database engine is laying down the data pages from the backup to disk, this is not instant file initialisation!

Instant file initialisation will be noticeable when adding new database files or growing database files.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Perry Whittle (1/4/2013)
During a restore the database engine is laying down the data pages from the backup to disk, this is not instant file initialisation!
Instant file initialisation will be noticeable when adding new database files or growing database files.

Instant file initialization is in play when doing a restore.

http://www.sqlskills.com/blogs/kimberly/instant-initialization-what-why-and-how/

http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

zi SSCrazy Points: 2387 More actions · Answer 3

Dear friend

Thanks for replying, is creating database also apply this option because I have created a new database by 50,000 MG but it tooks a very long time.

and sory for that , lot of articles on the net published

What Operations Are Affected Inside SQL Server

•Database Creation

•Add data file to existing database

•Growing a data file

•Restores

•Backup Operations

Thank you very much

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

See the section "How to Check if Instant File Initialization is Enabled" here: http://www.johnsansom.com/sqlserver-instant-file-initialization/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

zi SSCrazy Points: 2387 More actions · Answer 5

Thanks for replying

I followed the steps and wrote

USE master;

--Set Trace Flags 3004 and 3605 to On.

DBCC TRACEON(3004,-1);

DBCC TRACEON(3605,-1);

then I created a database by SSMS 1000 MG but it took a long time

I checked the sql error log I found zeoring completed....

any idea

Thanks lot

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

zi (1/4/2013)
Dear friend
Thanks for replying, is creating database also apply this option because I have created a new database by 50,000 MG but it tooks a very long time.
and sory for that , lot of articles on the net published
What Operations Are Affected Inside SQL Server
•Database Creation
•Add data file to existing database
•Growing a data file
•Restores
•Backup Operations
Thank you very much

Make sure the account that the sql server service runs under has the policy applied.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

zi SSCrazy Points: 2387 More actions · Answer 7

Thanks for replying

it works well when I added 2 useres : system and everyone ?

Thank you very much

zi SSCrazy Points: 2387 More actions · Answer 8

I restored a 5 GB database in 3.5 minites instead of 5 minites

is that good or it should be shorter ?

but creating database didnt take secondes ?

Thanks lot

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

Look at the SQL Server Error Log to see how long each step in the creation of a new database took. Post the messages here if you have doubts.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato