Skip to content

Instant File Initialisation

October 23, 2012

/*
benefits
Whenever a database is created. Space need to be allocated for the data file(s).
Whenever a data file is manually expanded in size.
Whenever you have auto-grow for a data file. Note that potentially some poor user will now sit and wait for the auto-grow to complete.
When you start SQL Server. Why? Tempdb need to be re-created.
When you perform restore, if the destination database not already exists with matching database file structure.

doesnt affect log files

*/
–USE master
–GO
–EXEC sp_configure ‘show advanced options’, 1
–GO
–RECONFIGURE WITH OVERRIDE;
–GO
–EXEC sp_configure ‘xp_cmdshell’, 1;
–GO
–RECONFIGURE WITH OVERRIDE;
–GO

CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));
GO

INSERT INTO #xp_cmdshell_output EXEC (‘xp_cmdshell ”whoami /priv”’);
GO

IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE ‘%SeManageVolumePrivilege%’)
PRINT ‘Instant Initialization enabled’
ELSE
PRINT ‘Instant Initialization disabled’;
GO

DROP TABLE #xp_cmdshell_output;
GO

–EXEC sp_configure ‘xp_cmdshell’, 0;
–GO
–RECONFIGURE WITH OVERRIDE;
–GO
–EXEC sp_configure ‘show advanced options’, 0
–GO
–RECONFIGURE WITH OVERRIDE;
–GO

 

http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: