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.
Before you begin: Recommendations, Security
To View the Diagnostic Log, using: SQL Server Management Studio, Transact-SQL
To Configure Diagnostic Log settings, using: Transact-SQL
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:
From the File menu, select Open, File, and choose the diagnostic log file you want to view.
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.
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.
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;