Number of (Error-)Log Files?

Question

question

eghetto asked Mar 5, '13

Number of (Error-)Log Files?

Hi!

How do I find out the number of my error log files?

I tried this:

DECLARE @NumErrorLogs INT;
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', 
@NumErrorLogs OUTPUT;
    SELECT @NumErrorLogs;

but I get an error:

RegQueryValueEx() returned error 2, 'The system cannot find the file specified.' Msg 22001, Level 1, State 1

sql-server-2008-r2 logfile

699-numberoflogs.png (6.2 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ · Jun 20, 2018 at 11:29 AM

I believe if the registry key does not exist then it is the default value of 6

1 ·

Answer 1 · 2013-03-11T09:44:11Z

eghetto answered Mar 11, '13

Here is my SQL solution, not pretty but working:

DECLARE @FileList AS TABLE (
     subdirectory NVARCHAR(4000) NOT NULL 
     ,DEPTH BIGINT NOT NULL
     ,[FILE] BIGINT NOT NULL
    );
    
    DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);
    SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));
    SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\';
    
    INSERT INTO @FileList
    EXEC xp_dirtree @ErrorLogPath, 0, 1;
    
    DECLARE @NumberOfLogfiles INT;
    SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');
    SELECT @NumberOfLogfiles;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2013-03-07T18:23:09Z

Tim answered Mar 7, '13

If the key doesn't exist, the undocumented stored procedure master.dbo.xp_instance_regread will return the error you are getting. Are you wanting to increase the number? What is the root issue you are trying to solve for? If you want to use TSQL to increase the number you can use something like

EXEC xp_instance_regwrite
	N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'NumErrorLogs',
	REG_DWORD,
	10

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto · Mar 08, 2013 at 06:29 AM

No, Tim - I don't want to increase the number. I want to read the logs - to be more precise: I want to read ALL the log (files). Therefor I have to know how many files exist. I'm using EXEC master.dbo.xp_readerrorlog @NumberOfLogfile

0 ·

question