SQL Server Error Log Configuration

Question

Dear Friends,

What is SQL Query to return the SQL Error Log Configuration?

Thanks in Advance

Answer 1

From running SQL Profiler whilst viewing that option in Management Studio it appears to be stored in the registry. The below is what I got. It returns NULL if I have the "limit the number of error log files..." option unchecked or the number otherwise.

If it doesn't work for you you might want to try the same thing on your instance to see if the paths are any different.

declare @HkeyLocal nvarchar(18)
declare @MSSqlServerRegPath nvarchar(31)
declare @InstanceRegPath sysname

select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

-- Instance-based paths
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 @NumErrorLogs