Arsthbt

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

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

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