Procedure: [AdventureWorks2016CTP3].[SQLCop].[test Instant File Initialization]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [SQLCop] 
 Owner   [dbo] 
 Creation date   19.09.2016 
 Type   P 
 Encrypted   
 ID   315864192 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Objects that [SQLCop].[test Instant File Initialization] depends on


Object name Object type Dep level
[SQLCop] Schema 1
[tSQLt].[Fail] Unknown 1
Total 2 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [SQLCop].[test Instant File Initialization]
AS
BEGIN
    -- Written by George Mastros
    -- February 25, 2012
    -- http://sqlcop.lessthandot.com
    -- http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/#more-1704
    
    SET NOCOUNT ON

    DECLARE @Output VarChar(max)
    SET @Output = ''

    CREATE TABLE #Output(Value VarChar(8000))

    If Exists(select * from sys.configurations Where name='xp_cmdshell' and value_in_use = 1)
        Begin
            If Is_SrvRoleMember('sysadmin') = 1
                Begin
                    Insert Into #Output EXEC ('xp_cmdshell ''whoami /priv''');

                    If Not Exists(Select 1 From #Output Where Value LIKE '%SeManageVolumePrivilege%')
                        Select @Output = 'Instant File Initialization disabled'
                    Else
                        Select    @Output = 'Instant File Initialization disabled'
                        From    #Output
                        Where    Value LIKE '%SeManageVolumePrivilege%'
                                And Value Like '%disabled%'
                End
            Else
                Set @Output = 'You do not have the appropriate permissions to run xp_cmdshell'
        End
    Else
        Begin
            Set @Output = 'xp_cmdshell must be enabled to determine if instant file initialization is enabled.'
        End
    Drop Table #Output

    If @Output > '' 
        Begin
            Set @Output = Char(13) + Char(10) 
                          + 'For more information:  '
                          + 'http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/#more-1704'
                          + Char(13) + Char(10) 
                          + Char(13) + Char(10) 
                          + @Output
            EXEC tSQLt.Fail @Output
        End  
END;

GO

Collapse image See also


List of procedures