Export (0) Print
Expand All

View and Read Failover Cluster Instance Diagnostics Log

 

All critical errors and warning events for the SQL Server Resource DLL are written to the Windows event log. A running log of the diagnostic information specific to SQL Server is captured by the sp_server_diagnostics (Transact-SQL) system stored procedure and is written to the SQL Server failover cluster diagnostics (also known as the SQLDIAG logs) log files.

By default, the SQLDIAG are stored under a local LOG folder of the SQL Server instance directory, for example, 'C\Program Files\Microsoft SQL Server\MSSQL13.<InstanceName>\MSSQL\LOG' of the owning node of the AlwaysOn Failover Cluster Instance (FCI). The size of each SQLDIAG log file is fixed at 100 MB. Ten such log files are stored on the computer before they are recycled for new logs.

The logs use the extended events file format. The sys.fn_xe_file_target_read_file system function can be used to read the files that are created by Extended Events. One event, in XML format, is returned per row. Query the system view to parse the XML data as a result-set. For more information, see sys.fn_xe_file_target_read_file (Transact-SQL).

VIEW SERVER STATE permission is needed to run fn_xe_file_target_read_file.

Open SQL Server Management Studio as Administrator

To view the Diagnostic log files:

  1. From the File menu, select Open, File, and choose the diagnostic log file you want to view.

  2. The events are displayed as rows in the right pane, and by default name, and timestamp are the only two columns displayed.

    This also activates the ExtendedEvents menu.

  3. To see more columns, go the ExtendedEvents menu, and select Choose Columns.

    A dialog box opens with the available columns allowing you to select the columns for display.

  4. You can filter, and sort the event data using the ExtendedEvents menu and selecting the Filter option.

To view the Diagnostic log files:

To view all the log items in the SQLDIAG log file, use the following query:

SELECT
xml_data.value('(event/@name)[1]','varchar(max)') AS 'Name'
,xml_data.value('(event/@package)[1]','varchar(max)') AS 'Package'
,xml_data.value('(event/@timestamp)[1]','datetime') AS 'Time'
,xml_data.value('(event/data[@name=''state'']/value)[1]','int') AS 'State'
,xml_data.value('(event/data[@name=''state_desc'']/text)[1]','varchar(max)') AS 'State Description'
,xml_data.value('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'Failure Conditions'
,xml_data.value('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name'
,xml_data.value('(event/data[@name=''instancename'']/value)[1]','varchar(max)') AS 'Instance Name'
,xml_data.value('(event/data[@name=''creation time'']/value)[1]','datetime') AS 'Creation Time'
,xml_data.value('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component'
,xml_data.value('(event/data[@name=''data'']/value)[1]','varchar(max)') AS 'Data'
,xml_data.value('(event/data[@name=''info'']/value)[1]','varchar(max)') AS 'Info'
FROM
 ( SELECT object_name AS 'event'
  ,CONVERT(xml,event_data) AS 'xml_data'
  FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLNODE1_MSSQLSERVER_SQLDIAG_0_129936003752530000.xel',NULL,NULL,NULL) 
) 
AS XEventData
ORDER BY Time; 

System_CAPS_noteNote

You can filter the results for specific components or state using the WHERE clause.

To configure the Diagnostic Log Properties

System_CAPS_noteNote

For an example of this procedure, see Example (Transact-SQL), later in this section.

Using the Data Definition Language (DDL) statement, ALTER SERVER CONFIGURATION, you can start or stop logging diagnostic data captured by the sp_server_diagnostics (Transact-SQL) procedure, and set SQLDIAG log configuration parameters such as the log file rollover count, log file size, and file location. For syntax details, see Setting diagnostic log options.

The examples in this section show how to set the values for the diagnostic log option.

The following example starts the logging of diagnostic data.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

The following example stops the logging of diagnostic data.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

The following example sets the location of the diagnostic logs to the specified file path.

ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG PATH = 'C:\logs';

The following example set the maximum size of each diagnostic log to 10 megabytes.

ALTER SERVER CONFIGURATION 
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft