Ttyjukl

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

.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

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

share|improve this question

asked 7 hours ago

BeginnerDBABeginnerDBA

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
  

  
  
  
  

add a comment | 

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

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

share|improve this question

asked 7 hours ago

BeginnerDBABeginnerDBA

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
  

  
  
  
  

add a comment | 

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

asked 7 hours ago

BeginnerDBABeginnerDBA

7041520

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

share|improve this question

asked 7 hours ago

BeginnerDBABeginnerDBA

7041520

share|improve this question

asked 7 hours ago

BeginnerDBABeginnerDBA

7041520

asked 7 hours ago

BeginnerDBABeginnerDBA

7041520

asked 7 hours ago

BeginnerDBABeginnerDBA

7041520

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

answered 5 hours ago

Doug DedenDoug Deden

4286

add a comment | 

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

answered 5 hours ago

Conrad S.Conrad S.

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
  
  
  

  
  
  
  

add a comment | 

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

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

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

Name

Email

Required, but never shown

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

answered 5 hours ago

Doug DedenDoug Deden

4286

add a comment | 

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

answered 5 hours ago

Doug DedenDoug Deden

4286

add a comment | 

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

answered 5 hours ago

Doug DedenDoug Deden

4286

share|improve this answer

answered 5 hours ago

Doug DedenDoug Deden

4286

answered 5 hours ago

Doug DedenDoug Deden

4286

answered 5 hours ago

Doug DedenDoug Deden

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

answered 5 hours ago

Conrad S.Conrad S.

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
  
  
  

  
  
  
  

add a comment | 

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

answered 5 hours ago

Conrad S.Conrad S.

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
  
  
  

  
  
  
  

add a comment | 

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

answered 5 hours ago

Conrad S.Conrad S.

584

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

answered 5 hours ago

Conrad S.Conrad S.

584

answered 5 hours ago

Conrad S.Conrad S.

584

answered 5 hours ago

Conrad S.Conrad S.

584