Mrthfx

tabularx column has extra padding at right?

Determine the generator of an ideal of ring of integers

Compiling and throwing simple dynamic exceptions at runtime for JVM

Protagonist's race is hidden - should I reveal it?

Has a Nobel Peace laureate ever been accused of war crimes?

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

What is the difference between 准时 and 按时?

Why does BitLocker not use RSA?

Checking IFI enabled on SQL server below 2016

Why not use the yoke to control yaw, as well as pitch and roll?

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

Is the Mordenkainen's Sword spell underpowered?

Can this water damage be explained by lack of gutters and grading issues?

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

What could prevent concentrated local exploration?

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

Pointing to problems without suggesting solutions

Proving inequality for positive definite matrix

When speaking, how do you change your mind mid-sentence?

What were wait-states, and why was it only an issue for PCs?

How to leave only the following strings?

Who's this lady in the war room?

Marquee sign letters

Married in secret, can marital status in passport be changed at a later date?

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