Checking IFI enabled on SQL server below 2016 Announcing the arrival of Valued Associate #679:...

Does traveling In The United States require a passport or can I use my green card if not a US citizen?

Can a Wizard take the Magic Initiate feat and select spells from the Wizard list?

Lights are flickering on and off after accidentally bumping into light switch

Why aren't these two solutions equivalent? Combinatorics problem

Recursive calls to a function - why is the address of the parameter passed to it lowering with each call?

What is the evidence that custom checks in Northern Ireland are going to result in violence?

Does Prince Arnaud cause someone holding the Princess to lose?

How to charge percentage of transaction cost?

false 'Security alert' from Google - every login generates mails from 'no-reply@accounts.google.com'

A German immigrant ancestor has a "Registration Affidavit of Alien Enemy" on file. What does that mean exactly?

How was Lagrange appointed professor of mathematics so early?

How to leave only the following strings?

What helicopter has the most rotor blades?

Kepler's 3rd law: ratios don't fit data

Can gravitational waves pass through a black hole?

Help Recreating a Table

Is Vivien of the Wilds + Wilderness Reclamation a competitive combo?

Will I be more secure with my own router behind my ISP's router?

What is her name?

Providing direct feedback to a product salesperson

Pointing to problems without suggesting solutions

Meaning of "Not holding on that level of emuna/bitachon"

Can I take recommendation from someone I met at a conference?

Compiling and throwing simple dynamic exceptions at runtime for JVM



Checking IFI enabled on SQL server below 2016



Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)MS SQL Server 2016 Oprimal Disk Configurationinstalling SQL SERVER 2016 side-by-side SQL Server 2012SSRS 2016 Connect to SQL Server with Force Encryption EnabledInstallation SQL Server 2016 errorSQL Server 2016 vs 2012 insert performanceSQL server 2016 - .mdmp fileSQL Server 2016 Enterprise poor performanceSQL Server 2016 expiration dateHow to remotely connect SQL Server 2016. Since we have 2012 and 2016 in same serverIntelliSense is enabled but not working in SQL Server 2016 with Azure SQL DB





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







1















I am using in one of my reports to check for status of IFI being enabled on SQL server.



It works well when using dmv sys.dm_server_services for SQL2016



However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.



For example if i use below



EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';


There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report










share|improve this question























  • I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

    – Aaron
    1 hour ago


















1















I am using in one of my reports to check for status of IFI being enabled on SQL server.



It works well when using dmv sys.dm_server_services for SQL2016



However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.



For example if i use below



EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';


There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report










share|improve this question























  • I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

    – Aaron
    1 hour ago














1












1








1








I am using in one of my reports to check for status of IFI being enabled on SQL server.



It works well when using dmv sys.dm_server_services for SQL2016



However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.



For example if i use below



EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';


There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report










share|improve this question














I am using in one of my reports to check for status of IFI being enabled on SQL server.



It works well when using dmv sys.dm_server_services for SQL2016



However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.



For example if i use below



EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';


There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report







sql-server sql-server-2012 sql-server-2014 sql-server-2016






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked 7 hours ago









BeginnerDBABeginnerDBA

7041520




7041520













  • I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

    – Aaron
    1 hour ago



















  • I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

    – Aaron
    1 hour ago

















I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

– Aaron
1 hour ago





I haven’t actually tested this but the post uses PowerShell to check if the specified user account has been granted the local policy “Perform Volume Maintenance Tasks”. mssqltips.com/sqlservertip/5240/…

– Aaron
1 hour ago










2 Answers
2






active

oldest

votes


















1














According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.




SELECT  @@SERVERNAME AS [Server Name] ,
RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
+ CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
service_account ,
instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'



For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."






share|improve this answer































    1














    Glenn Spies posted at SQLSkills.com this script.
    https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/



    I've used it a few times, and it works correctly.



    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





    share|improve this answer
























    • This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

      – Randolph West
      16 mins ago














    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235412%2fchecking-ifi-enabled-on-sql-server-below-2016%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.




    SELECT  @@SERVERNAME AS [Server Name] ,
    RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
    LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
    + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
    service_account ,
    instant_file_initialization_enabled
    FROM sys.dm_server_services
    WHERE servicename LIKE 'SQL Server (%'



    For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."






    share|improve this answer




























      1














      According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.




      SELECT  @@SERVERNAME AS [Server Name] ,
      RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
      LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
      + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
      service_account ,
      instant_file_initialization_enabled
      FROM sys.dm_server_services
      WHERE servicename LIKE 'SQL Server (%'



      For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."






      share|improve this answer


























        1












        1








        1







        According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.




        SELECT  @@SERVERNAME AS [Server Name] ,
        RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
        service_account ,
        instant_file_initialization_enabled
        FROM sys.dm_server_services
        WHERE servicename LIKE 'SQL Server (%'



        For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."






        share|improve this answer













        According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.




        SELECT  @@SERVERNAME AS [Server Name] ,
        RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
        service_account ,
        instant_file_initialization_enabled
        FROM sys.dm_server_services
        WHERE servicename LIKE 'SQL Server (%'



        For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 5 hours ago









        Doug DedenDoug Deden

        4286




        4286

























            1














            Glenn Spies posted at SQLSkills.com this script.
            https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/



            I've used it a few times, and it works correctly.



            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





            share|improve this answer
























            • This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

              – Randolph West
              16 mins ago


















            1














            Glenn Spies posted at SQLSkills.com this script.
            https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/



            I've used it a few times, and it works correctly.



            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





            share|improve this answer
























            • This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

              – Randolph West
              16 mins ago
















            1












            1








            1







            Glenn Spies posted at SQLSkills.com this script.
            https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/



            I've used it a few times, and it works correctly.



            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





            share|improve this answer













            Glenn Spies posted at SQLSkills.com this script.
            https://www.sqlskills.com/blogs/paul/how-to-tell-if-you-have-instant-initialization-enabled/



            I've used it a few times, and it works correctly.



            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






            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered 5 hours ago









            Conrad S.Conrad S.

            584




            584













            • This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

              – Randolph West
              16 mins ago





















            • This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

              – Randolph West
              16 mins ago



















            This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

            – Randolph West
            16 mins ago







            This is pretty neat, and I'm leveraging it. It does require xp_cmdshell, though that seems a non-issue with the OP's current usage of xp_readerrorlog.

            – Randolph West
            16 mins ago




















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235412%2fchecking-ifi-enabled-on-sql-server-below-2016%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Gersau Kjelder | Navigasjonsmeny46°59′0″N 8°31′0″E46°59′0″N...

            Hestehale Innhaldsliste Hestehale på kvinner | Hestehale på menn | Galleri | Sjå òg |...

            What is the “three and three hundred thousand syndrome”?Who wrote the book Arena?What five creatures were...