sqlnewbie17 (1/27/2016)
I can change the errorlog count (to 9)using thisUSE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 9
GO
But how do I read the number of error logs.
I am trying to read the number of configured/set SQLError logs using this.(default I believe is 6)
USE [master]
GO
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
GO
But it gives me this feedback when I run the above statement.
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
(0 row(s) affected)
Can I read the number of configured SQLError logs using the above statement.
If not how do I get the number of SQLError logs.
Thanks
You can. The trick is that that registry entry doesn't exist until you make a change to the setting.
Just output the result to a variable. Then, since 6 is the default (https://msdn.microsoft.com/en-us/library/ms177285.aspx) and the output variable will be NULL if the setting has not been changed, just use ISNULL to return a 6 in that case.
Something like this:
DECLARE @num_logs int;
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs',
@num_logs OUTPUT;
SELECT number_error_logs=ISNULL(@num_logs,6);
Cheers!