A short Tidbit about a DMV that I came across today.

When,troubleshooting any issue related to SQL Server, one of the first things we ask the customer  is the SQL Server ErrorLog. Many times, we get the question, where is it located?

There  are several ways to determine the path of the ErrorLog.

 My fellow Microsoft colleague Balmukund has a blog post explaining the various ways to find the path of the ErrorLog. 

http://sqlserver-help.com/2011/06/26/help-where-is-sql-server-errorlog/

Apart from the above described methods, One other way, that we could get the path of the ErrorLog  in SQL Server 2012,when the SQL Server service is running  is through this DMV. 

Sys.dm_os_server_diagnostics_log_configurations

 

SELECT is_enabled,[path],max_size,max_files

FROM

Sys.dm_os_server_diagnostics_log_configurations


Executing this DMV will give valuable information about the Errorlog  like

  •  Path indicating the location of the diagnostic logs.
  •  Maximum size in megabytes to which each of the diagnostic logs can grow.
  •  Maximum number of diagnostic log files that can be stored on the computer before they are recycled for new diagnostic logs.
  •  If logging is enabled or not.

 

Hope this helps!!