Retrieve Configured value SQL Error Log

Question

I want to retrieve the maximum number of error log file value using SQL Query.

Answer 1

From running SQL Server Profiler and tracing the calls this is what SSMS does.

declare @NumErrorLogs int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
                            N'Software\Microsoft\MSSQLServer\MSSQLServer',
                            N'NumErrorLogs', 
                            @NumErrorLogs OUTPUT
SELECT @NumErrorLogs

Answer 2

The number of Sql error log files setting is stored in the windows registry at "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS_ADV\MSSQLServer]\NumErrorLogs"

It can be retrieved using the following sql (from sql profiler).

 use master
    declare @HkeyLocal nvarchar(18)
    declare @MSSqlServerRegPath nvarchar(31)
    declare @InstanceRegPath sysname
    select @HkeyLocal=N'HKEY_LOCAL_MACHINE'
    select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
    select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
    declare @NumErrorLogs int
    exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT
    SELECT
    ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles]