Count SQLErrorLogs

• mtz676

  SSCrazy Eights

  Points: 9222

  More actions

  January 27, 2016 at 4:57 am

  #311130

  I can change the errorlog count (to 9)using this

  USE [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

• Sgar...

  SSCarpal Tunnel

  Points: 4409

  More actions

  January 29, 2016 at 2:43 am

  #1855165

  What is the exact requirement? Number of error logs is important or error log content. I think you can search using keywords like 'error' and 'fail'

  Sagar Sonawane
  ** Every DBA has his day!!:cool:

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 29, 2016 at 9:02 am

  #1855268

  sqlnewbie17 (1/27/2016)

  ---

  I can change the errorlog count (to 9)using this

  USE [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!