public void GetsRejectUpdateInsertThrottlingConditionFromSqlError()
{
SqlError sqlError = SqlExceptionCreator.GenerateFakeSqlError(ThrottlingCondition.ThrottlingErrorNumber, "Code: 12345 SQL Error");
ThrottlingCondition condition = ThrottlingCondition.FromError(sqlError);
Assert.AreEqual(ThrottlingMode.RejectUpdateInsert, condition.ThrottlingMode);
}
static void AugmentSqlExceptionWithThrottlingDetails(SqlError error, SqlException sqlException)
{
// https://msdn.microsoft.com/en-us/library/azure/ff394106.aspx
var throttlingCondition = ThrottlingCondition.FromError(error);
sqlException.Data[throttlingCondition.ThrottlingMode.GetType().Name] = throttlingCondition.ThrottlingMode.ToString();
sqlException.Data[throttlingCondition.GetType().Name] = throttlingCondition;
}
public void TestDecodeThrottlingReasonCode()
{
SqlError err = FakeSqlExceptionGenerator.GenerateFakeSqlError(ThrottlingCondition.ThrottlingErrorNumber, Resources.SampleThrottlingErrorMsg);
ThrottlingCondition condition = ThrottlingCondition.FromError(err);
Assert.AreEqual(ThrottlingMode.RejectAll, condition.ThrottlingMode, "Unexpected throttling mode.");
ThrottlingType throttlingType = condition.ThrottledResources.Where(x => x.Item1 == ThrottledResourceType.Cpu).Select(x => x.Item2).FirstOrDefault();
Assert.AreEqual(ThrottlingType.Hard, throttlingType, "Unexpected throttling type.");
}
/// <summary>
/// Determines whether the specified exception represents a transient failure that can be compensated by a retry.
/// </summary>
/// <param name="ex">The exception object to be verified.</param>
/// <returns>
/// true if the specified exception is considered as transient; otherwise, false.
/// </returns>
public bool IsTransient(Exception ex)
{
if (ex != null)
{
SqlException sqlException;
if ((sqlException = ex as SqlException) != null)
{
foreach (SqlError error in sqlException.Errors)
{
switch (error.Number)
{
case 40501:
ThrottlingCondition throttlingCondition = ThrottlingCondition.FromError(error);
sqlException.Data[throttlingCondition.ThrottlingMode.GetType().Name] = ((object)throttlingCondition.ThrottlingMode).ToString();
sqlException.Data[throttlingCondition.GetType().Name] = throttlingCondition;
return(true);
case 40540:
case 40613:
case 10928:
case 10929:
case 40143:
case 40197:
case 233:
case 10053:
case 10054:
case 10060:
case 20:
case 64:
return(true);
default:
continue;
}
}
}
else
{
if (ex is TimeoutException)
{
return(true);
}
EntityException entityException;
if ((entityException = ex as EntityException) != null)
{
return(IsTransient(entityException.InnerException));
}
}
}
return(false);
}
/// <summary>
/// Indicates if is a SqlException.
/// </summary>
/// <param name="ex">An exception.</param>
/// <returns>True if SqlException, false otherwise.</returns>
public bool IsTransient(Exception ex)
{
if (ex != null)
{
SqlException exception = ex as SqlException;
if (exception != null)
{
foreach (SqlError error in exception.Errors)
{
switch (error.Number)
{
case 0x2745:
case 0x2746:
case 0x274c:
case 0xe9:
case 20:
case 0x40:
case 0x2ab0:
case 0x2ab1:
case 0x9ccf:
case 0x9d05:
case 0x9e5c:
case 0x9ea5:
return(true);
case 0x9e35:
{
ThrottlingCondition condition = ThrottlingCondition.FromError(error);
exception.Data[condition.ThrottlingMode.GetType().Name] = condition.ThrottlingMode.ToString();
exception.Data[condition.GetType().Name] = condition;
return(true);
}
}
}
}
else
{
if (ex is TimeoutException)
{
return(true);
}
EntityException exception2 = ex as EntityException;
if (exception2 != null)
{
return(this.IsTransient(exception2.InnerException));
}
}
}
return(false);
}
/// <summary>
/// Determines the throttling conditions from the specified reason code.
/// </summary>
/// <param name="reasonCode">The reason code returned by SQL Database that contains the throttling mode and the exceeded resource types.</param>
/// <returns>An instance of the object holding the decoded reason codes returned from SQL Database when encountering throttling conditions.</returns>
public static ThrottlingCondition FromReasonCode(int reasonCode)
{
if (reasonCode > 0)
{
// Decode throttling mode from the last 2 bits.
var throttlingMode = (ThrottlingMode)(reasonCode & 3);
var condition = new ThrottlingCondition {
ThrottlingMode = throttlingMode
};
// Shift 8 bits to truncate throttling mode.
var groupCode = reasonCode >> 8;
// Determine throttling type for all well-known resources that may be subject to throttling conditions.
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.PhysicalDatabaseSpace,
(ThrottlingType)(groupCode & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.PhysicalLogSpace,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.LogWriteIoDelay,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.DataReadIoDelay,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Cpu,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.DatabaseSize,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Internal,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.WorkerThreads,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Internal,
(ThrottlingType)((groupCode >> 2) & 3)));
return(condition);
}
return(Unknown);
}
[Ignore] // REVIEW - Test will continue throwing the exception so the action will eventually run out of retry attempts
public void TestThrottlingConditionSimulation()
{
// Instantiate a retry policy capable of detecting transient faults that are specific to SQL Database. Use the default retry count.
RetryPolicy retryPolicy = new RetryPolicy <SqlDatabaseTransientErrorDetectionStrategy>(RetryStrategy.DefaultClientRetryCount);
// Register a custom event handler that will be invoked by the policy whenever a retry condition is encountered.
retryPolicy.Retrying += (sender, args) =>
{
// Log a warning message to indicate that a retry loop kicked in.
Trace.TraceWarning("Retry condition encountered. Reason: {0} (retry count: {1}, retry delay: {2})", args.LastException.Message, args.CurrentRetryCount, args.Delay);
// Make sure we are dealing with a SQL exception.
if (args.LastException is SqlException sqlException)
{
// Parse the exception to find out whether it is indicative of any throttling conditions.
ThrottlingCondition throttlingCondition = ThrottlingCondition.FromException(sqlException);
// Verify whether throttling condition were detected.
if (!throttlingCondition.IsUnknown)
{
// Log a further warning message with details on throttling conditions encountered.
Trace.TraceWarning("Throttling condition detected. Details: {0}", throttlingCondition);
}
}
};
// Invoke a database operation or set of database operations against SQL Database.
retryPolicy.ExecuteAction(() =>
{
// Open a connection, execute a SQL query or stored procedure, perform any kind of database operations.
// ...
SqlException sqlEx = FakeSqlExceptionGenerator.GenerateFakeSqlException(ThrottlingCondition.ThrottlingErrorNumber, Resources.SampleThrottlingErrorMsg);
throw sqlEx;
});
}
/// <summary>
/// Determines whether the specified exception represents a transient failure that can be compensated by a retry.
/// </summary>
/// <param name="ex">The exception object to be verified.</param>
/// <returns>true if the specified exception is considered as transient; otherwise, false.</returns>
public bool IsTransient(Exception ex)
{
if (ex != null)
{
SqlException sqlException;
if ((sqlException = ex as SqlException) != null)
{
// Enumerate through all errors found in the exception.
foreach (SqlError err in sqlException.Errors)
{
switch (err.Number)
{
// SQL Error Code: 40501
// The service is currently busy. Retry the request after 10 seconds. Code: (reason code to be decoded).
case ThrottlingCondition.ThrottlingErrorNumber:
// Decode the reason code from the error message to determine the grounds for throttling.
var condition = ThrottlingCondition.FromError(err);
// Attach the decoded values as additional attributes to the original SQL exception.
sqlException.Data[condition.ThrottlingMode.GetType().Name] =
condition.ThrottlingMode.ToString();
sqlException.Data[condition.GetType().Name] = condition;
return(true);
// SQL Error Code: 10928
// Resource ID: %d. The %s limit for the database is %d and has been reached.
case 10928:
// SQL Error Code: 10929
// Resource ID: %d. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d.
// However, the server is currently too busy to support requests greater than %d for this database.
case 10929:
// SQL Error Code: 10053
// A transport-level error has occurred when receiving results from the server.
// An established connection was aborted by the software in your host machine.
case 10053:
// SQL Error Code: 10054
// A transport-level error has occurred when sending the request to the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 10054:
// SQL Error Code: 10060
// A network-related or instance-specific error occurred while establishing a connection to SQL Server.
// The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server
// is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed
// because the connected party did not properly respond after a period of time, or established connection failed
// because connected host has failed to respond.)"}
case 10060:
// SQL Error Code: 40197
// The service has encountered an error processing your request. Please try again.
case 40197:
// SQL Error Code: 40540
// The service has encountered an error processing your request. Please try again.
case 40540:
// SQL Error Code: 40613
// Database XXXX on server YYYY is not currently available. Please retry the connection later. If the problem persists, contact customer
// support, and provide them the session tracing ID of ZZZZZ.
case 40613:
// SQL Error Code: 40143
// The service has encountered an error processing your request. Please try again.
case 40143:
// SQL Error Code: 233
// The client was unable to establish a connection because of an error during connection initialization process before login.
// Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy
// to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 233:
// SQL Error Code: 64
// A connection was successfully established with the server, but then an error occurred during the login process.
// (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
case 64:
// DBNETLIB Error Code: 20
// The instance of SQL Server you attempted to connect to does not support encryption.
case (int)ProcessNetLibErrorCode.EncryptionNotSupported:
return(true);
}
}
}
else if (ex is TimeoutException)
{
return(true);
}
else
{
EntityException entityException;
if ((entityException = ex as EntityException) != null)
{
return(this.IsTransient(entityException.InnerException));
}
}
}
return(false);
}
/// <summary>
/// Determines the throttling conditions from the specified reason code.
/// </summary>
/// <param name="reasonCode">The reason code returned by SQL Database that contains the throttling mode and the exceeded resource types.</param>
/// <returns>An instance of the object holding the decoded reason codes returned from SQL Database when encountering throttling conditions.</returns>
public static ThrottlingCondition FromReasonCode(int reasonCode)
{
if (reasonCode > 0)
{
// Decode throttling mode from the last 2 bits.
var throttlingMode = (ThrottlingMode)(reasonCode & 3);
var condition = new ThrottlingCondition { ThrottlingMode = throttlingMode };
// Shift 8 bits to truncate throttling mode.
var groupCode = reasonCode >> 8;
// Determine throttling type for all well-known resources that may be subject to throttling conditions.
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.PhysicalDatabaseSpace,
(ThrottlingType)(groupCode & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.PhysicalLogSpace,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.LogWriteIoDelay,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.DataReadIoDelay,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Cpu,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.DatabaseSize,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Internal,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.WorkerThreads,
(ThrottlingType)((groupCode = groupCode >> 2) & 3)));
condition._throttledResources.Add(Tuple.Create(ThrottledResourceType.Internal,
(ThrottlingType)((groupCode >> 2) & 3)));
return condition;
}
return Unknown;
}
// this is a specific error class we need to look for see http://technet.microsoft.com/en-us/library/aa937483(v=SQL.80).aspx
#region ITransientErrorDetectionStrategy implementation
/// <summary>
/// Determines whether the specified exception represents a transient failure that can be compensated by a retry.
/// </summary>
/// <param name="ex">The exception object to be verified.</param>
/// <returns>true if the specified exception is considered as transient; otherwise, false.</returns>
public bool IsTransient(Exception?ex)
{
switch (ex)
{
case null:
return(false);
case SqlException sqlException:
{
// Enumerate through all errors found in the exception.
foreach (SqlError err in sqlException.Errors)
{
switch (err.Number)
{
// SQL Error Code: 40501
// The service is currently busy. Retry the request after 10 seconds. Code: (reason code to be decoded).
case ThrottlingCondition.ThrottlingErrorNumber:
// Decode the reason code from the error message to determine the grounds for throttling.
ThrottlingCondition condition = ThrottlingCondition.FromError(err);
// Attach the decoded values as additional attributes to the original SQL exception.
sqlException.Data[condition.ThrottlingMode.GetType().Name] =
condition.ThrottlingMode.ToString();
sqlException.Data[condition.GetType().Name] = condition;
return(true);
case 0:
if ((err.Class == 20 || err.Class == 11) && err.State == 0 && err.Server is not null && ex.InnerException is null)
{
if (string.Equals(err.Message, Resources.SQL_SevereError, StringComparison.CurrentCultureIgnoreCase))
{
return(true);
}
}
return(false);
// SQL Error Code: 4060
// Cannot open database "%.*ls" requested by the login. The login failed.
case 4060:
// SQL Error Code: 10928
// Resource ID: %d. The %s limit for the database is %d and has been reached.
case 10928:
// SQL Error Code: 10929
// Resource ID: %d. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d.
// However, the server is currently too busy to support requests greater than %d for this database.
case 10929:
// SQL Error Code: 10053
// A transport-level error has occurred when receiving results from the server.
// An established connection was aborted by the software in your host machine.
case 10053:
// SQL Error Code: 10054
// A transport-level error has occurred when sending the request to the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 10054:
// SQL Error Code: 10060
// A network-related or instance-specific error occurred while establishing a connection to SQL Server.
// The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server
// is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed
// because the connected party did not properly respond after a period of time, or established connection failed
// because connected host has failed to respond.)"}
case 10060:
// SQL Error Code: 40197
// The service has encountered an error processing your request. Please try again.
case 40197:
// SQL Error Code: 40540
// The service has encountered an error processing your request. Please try again.
case 40540:
// SQL Error Code: 40544
// The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.
case 40544:
// SQL Error Code: 40549
// Session is terminated because you have a long-running transaction. Try shortening your transaction.
case 40549:
// SQL Error Code: 40550
// The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction.
case 40550:
// SQL Error Code: 40551
// The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce the temporary table space usage.
case 40551:
// SQL Error Code: 40552
// The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.
case 40552:
// SQL Error Code: 40553
// The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.
case 40553:
// SQL Error Code: 40613
// Database XXXX on server YYYY is not currently available. Please retry the connection later. If the problem persists, contact customer
// support, and provide them the session tracing ID of ZZZZZ.
case 40613:
// SQL Error Code: 40143
// The service has encountered an error processing your request. Please try again.
case 40143:
// SQL Error Code: 233
// The client was unable to establish a connection because of an error during connection initialization process before login.
// Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy
// to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server.
// (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
case 233:
// SQL Error Code: 64
// A connection was successfully established with the server, but then an error occurred during the login process.
// (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
case 64:
// DBNETLIB Error Code: 20
// The instance of SQL Server you attempted to connect to does not support encryption.
case (int)ProcessNetLibErrorCode.EncryptionNotSupported:
return(true);
}
}
break;
}
case TimeoutException:
return(true);
#if NETSTANDARD2_1 || NET5_0
case EntityException entityException:
return(this.IsTransient(entityException.InnerException));
#endif
}
return(false);
}