Monthly Archives: October 2012

Instant File Initialisation

/*
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

VLF

Worth considering for performance (will impact mirroring) : shrink tlog to reduce number of virtual log files (VLF)

Doesn’t take much to do and is one off, but you have to do it at a quiet time to get best effect

Reckon around 50 to be a good number

http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

 

NB dont run dbcc loginfo on a snapshot in SQL 2005!

http://connect.microsoft.com/SQLServer/feedback/details/292136/running-dbcc-loginfo-on-db-snapshot