How-to: Decode Reason Codes in SQL Azure
Microsoft SQL Azure Database is the relational database service on the Windows Azure platform. SQL Azure Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure Database customers, your connection may be closed. When your connection to the service is closed, you will receive one of the following errors: 40197, 40501, 40544, 40549, 40550, 40551, 40552, 40553, 40613, and 10053. We recommend that you implement the retry logic in your SQL Azure applications to catch these errors when possible. When an error occurs, re-establish the connection and then re-execute the failed commands or the query.
For more information on how to prevent connection-losses, see Connection Management in SQL Azure article in the TechNet Wiki. For a list of connection-loss errors, see Connection-Loss Errors.
This topic provides a code example that demonstrates how to decode the reason codes returned by error code 40501 “The service is currently busy. Retry the request after 10 seconds. Code: %d.”. The reason code (Code: %d) is a decimal number that contains the throttling mode and the exceeded resource type(s). The throttling mode enumerates the rejected statement types. The resource type specifies the exceeded resources. To understand the returned reason codes, you must first convert the 4-byte integer reason code to its hexadecimal representation. Then, you must analyze the first three bytes from right to left. For example, when the reason code is 0x00HHHHMM, MM represents the throttling mode and HHHH represents the resource type. For more information on reason codes, see Reason Codes.
Decoding Reason Codes
The following C# method gets a collection of one or more SqlError objects that give detailed information about exceptions generated by the .NET Framework Data Provider for SQL Server. The method calls the DecodeReasonCodes method when the error number is 40501.
public static void GetSqlErrors(SqlException sqlException)
{
foreach (SqlError error in sqlException.Errors)
{
if (error.Number == 40501)
{
DecodeReasonCodes(error);
}
}
}
The DecodeReasonCodes method parses the error code and finds the reason code. Then, it displays the throttling mode and the exceeded resource type by decoding the reason code. The code example demonstrates how to find out the throttling resource and severity levels for CPU, I/O, log space problems explicitly.
public static void DecodeReasonCodes(SqlError sqlError)
{
// Get the throttling error code (or reason code) from the error message.
Match m = Regex.Match(sqlError.Message, @"Code: (?<errorcode>[\w:]+)");
int reasonCode = 0;
if (!Int32.TryParse(m.Groups["errorcode"].Value, out reasonCode))
{
Console.WriteLine("Unable to get the throttling error code out of the error message.");
return;
}
// Decode mode from the last 2 bits.
string mode = null;
switch (reasonCode & 3)
{
case 0:
mode = "Allow all";
break;
case 1:
mode = "Prevent data growth";
break;
case 2:
mode = "Reject all writes";
break;
case 3:
mode = "Reject all";
break;
}
if (!String.IsNullOrEmpty(mode))
Console.WriteLine("Throttling mode : " + mode);
// Decode for CPU problems.
// 0x10: High-volume CPU activity exists.
int CPU_HIGH_MASK = (/*CPU Resource Type Code*/ 0x10) * (/*high*/ 0x100) * (/*default shift*/ 0x100); // 0001 0000 - 0000 0000 - 0000 0000
int CPU_MILD_MASK = (/*CPU Resource Type Code*/ 0x10) * (/*mild*/ 0x001) * (/*default shift*/ 0x100); // 0000 0000 - 0001 0000 - 0000 0000
// For example, the reasonCode is 131075 (decimal) in the following error message:
// "The service is currently busy. Retry the request after 10 seconds. Code: 131075."
if ((reasonCode & CPU_HIGH_MASK) > 0)
Console.WriteLine("Resource code: Excessively used CPU");
if ((reasonCode & CPU_MILD_MASK) > 0)
Console.WriteLine("Resource code: Mildy used CPU");
// Decode for I/O problems.
// 0x04: High-volume transaction/write/update activity exists.
int IO_HIGH_MASK = (/*IO Resource Type Code*/ 0x04) * (/*high*/ 0x100) * (/*default shift*/ 0x100); // 0000 0100 - 0000 0000 - 0000 0000
int IO_MILD_MASK = (/*IO Resource Type Code*/ 0x04) * (/*mild*/ 0x001) * (/*default shift*/ 0x100); // 0000 0000 - 0000 0100 - 0000 0000
if ((reasonCode & IO_HIGH_MASK) > 0)
Console.WriteLine("Resource code: Excessively used Log IO");
if ((reasonCode & IO_MILD_MASK) > 0)
Console.WriteLine("Resource code: Mildy used Log IO");
// Decode for log space problems.
// 0x02: Temporary log space problem occurred.
int LOG_HIGH_MASK = (/*LOG Resource Type Code*/ 0x02) * (/*high*/ 0x100) * (/*default shift*/ 0x100); // 0000 0100 - 0000 0000 - 0000 0000
int LOG_MILD_MASK = (/*LOG Resource Type Code*/ 0x02) * (/*mild*/ 0x001) * (/*default shift*/ 0x100); // 0000 0000 - 0000 0100 - 0000 0000
if ((reasonCode & LOG_HIGH_MASK) > 0)
Console.WriteLine("Resource code: Excessively used temporary log space");
if ((reasonCode & LOG_MILD_MASK) > 0)
Console.WriteLine("Resource code: Mildy used temporary log space");
}
