Vjylku

Raising a bilingual kid. When should we introduce the majority language?

Why is one lightbulb in a string illuminated?

Should man-made satellites feature an intelligent inverted "cow catcher"?

What is the difference between 准时 and 按时?

Coin Game with infinite paradox

How do I deal with an erroneously large refund?

Why isn't everyone flabbergasted about Bran's "gift"?

Is there a verb for listening stealthily?

Marquee sign letters

Why did Bronn offer to be Tyrion Lannister's champion in trial by combat?

Why are two-digit numbers in Jonathan Swift's "Gulliver's Travels" (1726) written in "German style"?

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

2 sample t test for sample sizes - 30,000 and 150,000

Does using the Inspiration rules for character defects encourage My Guy Syndrome?

If gravity precedes the formation of a solar system, where did the mass come from that caused the gravity?

Why does BitLocker not use RSA?

What documents does someone with a long-term visa need to travel to another Schengen country?

What's the difference between using dependency injection with a container and using a service locator?

Does Prince Arnaud cause someone holding the Princess to lose?

What helicopter has the most rotor blades?

How to break 信じようとしていただけかも知れない into separate parts?

How to make an animal which can only breed for a certain number of generations?

Compiling and throwing simple dynamic exceptions at runtime for JVM

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

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
  18 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
  18 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
  18 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