View and Read Failover Cluster Instance Diagnostics Log

  • Article

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.

Before You Begin

Recommendations

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\MSSQL12.<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).

Security

Permissions

VIEW SERVER STATE permission is needed to run fn_xe_file_target_read_file.

Open SQL Server Management Studio as Administrator

Using SQL Server Management Studio

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.

Using Transact-SQL

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\MSSQL12.MSSQLSERVER\MSSQL\Log\SQLNODE1_MSSQLSERVER_SQLDIAG_0_129936003752530000.xel',NULL,NULL,NULL)   
)   
AS XEventData  
ORDER BY Time;

Note

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

Using Transact-SQL

To configure the Diagnostic Log Properties

Note

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.

Examples (Transact-SQL)

Setting diagnostic log options

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

A. Starting diagnostic logging

The following example starts the logging of diagnostic data.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;
B. Stopping diagnostic logging

The following example stops the logging of diagnostic data.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;
C. Specifying the location of the diagnostic logs

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

ALTER SERVER CONFIGURATION  
SET DIAGNOSTICS LOG PATH = 'C:\logs';
D. Specifying the maximum size of each diagnostic log

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

ALTER SERVER CONFIGURATION   
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

See Also

Failover Policy for Failover Cluster Instances