Sql-server – SQLDIAG File in the SQL log folder

clusteringsql server

There were few inconsistency issues in our database and this happened because of unsuccessful fail-over of DB server(WSFC), same was asked in this question and is resolved last night after dropping and recreating primary key and moving data to another table.

There were many SQLDump files created before consistency error was fixed, its not getting created anymore however I see few SQLDIAG files in the log folder, opened the same using SSMS and it is set of events with name and timestamp, somewhere state_desc is appearing as clean whereas somewhere unknown or warning.

ServerName_InstanceName_SQLDIAG

Event name and Timestamp

Can someone please help me understanding this and if its an issue which needs to be resolved?

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) – 12.0.6024.0
(X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
(Build 9600: ) (Hypervisor)

Best Answer

This is the output of sp_server_diagnostics which is run to do health checking of either the primary replica in an availability group or an FCI. These are normal files to have and can help when investigating issues with availability groups or FCIs when dealing with failover.

Depending on the component and state, it may be an issue or may not be an issue.

  • Clean - No issues
  • Warning - There have been some key metrics above what is expected
  • Error - When a policy has been violated and a failure should be taken

Only System, Resource, and Query Processing are involved in the failover policies. Events will always say unknown and io_subsystem will only ever be clean or warning. You can read more about this here for AG or FCI.

Whether or not any of the items are an issue is another matter which can only be ascertained by the frame of which the issue occurred. Obviously if any say Error, that's a problem, but warning requires a frame of reference to understand if it's something that should be further looked into or disregarded.

Related Question