question

eghetto avatar image
eghetto asked

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-r2logfile
1 comment
10 |1200 characters needed characters left characters exceeded

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

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

1 Like 1 ·
eghetto avatar image
eghetto answered

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 characters needed characters left characters exceeded

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

Tim avatar image
Tim answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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