Kdtyjb

Proving inequality for positive definite matrix

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

"Destructive force" carried by a B-52?

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

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

Marquee sign letters

What is the difference between 准时 and 按时?

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

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

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

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

Can the van der Waals coefficients be negative in the van der Waals equation for real gases?

How was Lagrange appointed professor of mathematics so early?

Checking IFI enabled on SQL server below 2016

Why does my GNOME settings mention "Moto C Plus"?

Unix AIX passing variable and arguments to expect and spawn

How to ask rejected full-time candidates to apply to teach individual courses?

Putting Ant-Man on house arrest

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

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

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

What helicopter has the most rotor blades?

Is "ein Herz wie das meine" an antiquated or colloquial use of the possesive pronoun?

Is it OK if I do not take the receipt in Germany?

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 6 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 6 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 6 hours ago

BeginnerDBABeginnerDBA

7041520

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

share|improve this question

asked 6 hours ago

BeginnerDBABeginnerDBA

7041520

share|improve this question

asked 6 hours ago

BeginnerDBABeginnerDBA

7041520

asked 6 hours ago

BeginnerDBABeginnerDBA

7041520

asked 6 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
  8 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
  8 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
  8 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