Checking IFI enabled on SQL server below 2016
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
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
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
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
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
sql-server sql-server-2012 sql-server-2014 sql-server-2016
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
asked 6 hours ago
BeginnerDBABeginnerDBA
7041520
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 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
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
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 |
2 Answers
2
active
oldest
votes
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."
answered 5 hours ago
Doug DedenDoug Deden
4286
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
answered 5 hours ago
Conrad S.Conrad S.
584
This is pretty neat, and I'm leveraging it. It does requirexp_cmdshell, though that seems a non-issue with the OP's current usage ofxp_readerrorlog.
– Randolph West
9 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
});
}
});
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
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
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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."
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."
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."
answered 5 hours ago
Doug DedenDoug Deden
4286
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."
answered 5 hours ago
Doug DedenDoug Deden
4286
answered 5 hours ago
Doug DedenDoug Deden
4286
answered 5 hours ago
Doug DedenDoug Deden
4286
answered 5 hours ago
Doug DedenDoug Deden
4286
4286
add a comment |
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
answered 5 hours ago
Conrad S.Conrad S.
584
This is pretty neat, and I'm leveraging it. It does requirexp_cmdshell, though that seems a non-issue with the OP's current usage ofxp_readerrorlog.
– Randolph West
9 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
answered 5 hours ago
Conrad S.Conrad S.
584
This is pretty neat, and I'm leveraging it. It does requirexp_cmdshell, though that seems a non-issue with the OP's current usage ofxp_readerrorlog.
– Randolph West
9 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
answered 5 hours ago
Conrad S.Conrad S.
584
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
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
answered 5 hours ago
Conrad S.Conrad S.
584
584
This is pretty neat, and I'm leveraging it. It does requirexp_cmdshell, though that seems a non-issue with the OP's current usage ofxp_readerrorlog.
– Randolph West
9 mins ago
add a comment |
This is pretty neat, and I'm leveraging it. It does requirexp_cmdshell, though that seems a non-issue with the OP's current usage ofxp_readerrorlog.
– Randolph West
9 mins ago
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
9 mins ago
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
9 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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