Forum Discussion

OpsRaven's avatar
OpsRaven
Copper Contributor
Jun 11, 2019

Query for when a SQL server node failover from Primary to secondary

Hello, I am fairly new to Log Analytic's query language. I am trying to figure out a query to show when a SQL server node failover from Primary to Secondary. The server is connected within Azure. An...
azure monitor
Query Language
CliveWatson's avatar
CliveWatson
Former Employee
Jun 11, 2019

OpsRaven 

 

SQL Event IDs are well documented: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-2017 however there are a lot of them!

 

I'd guess ones like 13221-14223 might help - but someone with SQL experience might have to conform if you cant observe these yourself in the logs.

 

 

// find 3 named Event Ids

Event
| where EventID in (13221,13222,13223)
| summarize count() by EventID, RenderedDescription

or maybe look in your work space with a query below, or run it in the demo system, online to see what it does?

 

https://portal.loganalytics.io/Demo?q=H4sIAAAAAAAAA3MtS80r4eWqUShOTSxKzlBQCg70UQLzS3NzE4syq1IVkioVXEGqPF10FIJS81JSi1JTXFKLk4syC0oy8%2FO4ACgPnRBDAAAA&timespan=P1D

 

Event
| search "SQL"
| summarize by EventID, RenderedDescription

https://portal.loganalytics.io/Demo?q=H4sIAAAAAAAAA3MtS80r4eWqUShOTSxKzlBQCg70UQLzS3NzE4syq1IVkioVXEGqPF10FIJS81JSi1JTXFKLk4syC0oy8%2FNAinMyczNLFAy5APJsu1pOAAAA&timespan=P1D
Example output:

 

EventID RenderedDescription
59006 [Warning] CredentailManagementSupportedCheckFailed: System.Exception: Exception while getting version of sql server. ---> Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.IaaSAgentSqlQueryException: SQL number: 18456, SQL error: Login failed for user 'CONTOSORETAIL\CONTOSOSQLSRV2$'. ---> System.ServiceModel.FaultException`1[Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.SqlQueryExceptionFault]: The creator of this fault did not specify a Reason. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Contract.ISqlQueryService.GetSqlServerProperties() at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls[T](Func`2 func, Int32 retryCount, Int32 retryIntervalInSeconds) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.IaaSAgentSqlQuery.Client.IaaSAgentSqlQueryClient.HandleSqlQueryCalls[T](Func`2 func, Int32 retryCount, Int32 retryIntervalInSeconds) at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementQueries.GetSqlServerProperties() at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.IsSqlSupportedVersion() at Microsoft.SqlServer.Management.CredentialManagement.CredentialManagementHelpers.CheckIfCredentialManagementSupported() --- End of inner exception stack trace ---

 

 

 

Resources