x

Number of (Error-)Log Files?

Hi!

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

alt text

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

numberoflogs.png (6.3 kB)
more ▼

asked Mar 05, 2013 at 07:19 AM in Default

avatar image

eghetto
2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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;
more ▼

answered Mar 11, 2013 at 09:44 AM

avatar image

eghetto
2.2k 18 24 31

(comments are locked)
10|1200 characters needed characters left

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   
more ▼

answered Mar 07, 2013 at 06:23 PM

avatar image

Tim
40.9k 39 92 168

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
Mar 08, 2013 at 06:29 AM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x782
x7

asked: Mar 05, 2013 at 07:19 AM

Seen: 3598 times

Last Updated: Mar 11, 2013 at 09:44 AM

Copyright 2017 Redgate Software. Privacy Policy