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
I believe if the registry key does not exist then it is the default value of 6
Best Answer
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;
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
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
