3

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

  • 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 Apr 23 at 2:35
4

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."

4

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

EXEC sp_configure ‘xp_cmdshell’, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure ‘show advanced options’, 0
GO
RECONFIGURE WITH OVERRIDE;
GO
  • 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 Apr 23 at 3:47
  • @Conrad please add small script to change xp_cmdshell run value to 0 at the end of the script, please note you enabled it in the beginning. – Shanky Apr 23 at 8:31
  • @Shanky, I added those lines. Thanks. They were in the original. – Conrad S. Apr 23 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.