Logging transient errors

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 13-Sep-2016 22:47:15   

I'd like to do the same thing that was discussed in this thread (and for the same reason!).

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=23061

That is, create a log entry whenever there is a recovery attempt. I created an entry as recommended in the linked post but I'm not getting any output for that switch unless I have the ORMQueryExecution switch at either 3 or 4. The trace switch for the DQE is working correctly.


  <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="0" />
      <add name="Transient Error Recovery" value="4" /> 
      <add name="ORMGeneral" value="0" />
      <add name="ORMQueryExecution" value="3" />
     </switches>
    <trace autoflush="true">
      <listeners>
        <add name="TextListener"
             type="System.Diagnostics.TextWriterTraceListener"
             initializeData="c:\temp\App-Trace.txt" />
      </listeners>
    </trace>    
  </system.diagnostics>

My preference would be to have the ability to log this via code. Do I need to create a new class derived from RecoveryStrategyBase?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 14-Sep-2016 07:58:42   

If you dont want to use the .config then yes, the way to go would be to subclass the recoveryStrategy class and write what youeed when you need it inside such class.

David Elizondo | LLBLGen Support Team
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 08-Oct-2016 02:37:50   

Here is my initial attempt for anyone else who might be trying to do the same thing with SQL Server. (I have a similar class for DB2).

I've identified a group of exceptions that are not recoverable so we don't want to retry the query. I'm sure this group will need to grow over time. The logging should allow us to see how often the errors are occurring and which ones are not recoverable. We can add those as we go along.

This class goes in the DBSpecific project.


   public class RetryRecoveryStrategy : RecoveryStrategyBase
    {
        private static readonly NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();

        /// <summary>
        /// Initializes a new instance of the <see cref="RetryRecoveryStrategy"/> class.
        /// </summary>
        public RetryRecoveryStrategy()
            : base()
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="RetryRecoveryStrategy"/> class.
        /// </summary>
        /// <param name="maximumNumberOfRetries">The maximum number of retries.</param>
        /// <param name="delayCalculator">The delay calculator.</param>
        public RetryRecoveryStrategy(int maximumNumberOfRetries, RecoveryDelay delayCalculator)
            : base(maximumNumberOfRetries, delayCalculator)
        {
        }

        /// <summary>
        /// Determines whether the specified exception is a transient exception.
        /// </summary>
        /// <param name="toCheck">The exception to check.</param>
        /// <returns>
        /// true if the exception is a transient exception and can be retried, false otherwise. The empty implementation
        /// returns false.
        /// </returns>
        protected override bool IsTransientException(Exception toCheck)
        {
            if (toCheck is TimeoutException)
            {
                logger.Warn("Query timeout exception, attempting recovery if we have not already hit the max for this strategy.");
                return true;
            }


            var toCheckAsSqlException = toCheck as SqlException;
            if (toCheckAsSqlException == null)
            {
                return false;
            }

            // traverse all errors in the errors collection, as it might be the transient error is burried under another error.
            foreach (SqlError error in toCheckAsSqlException.Errors)
            {
                switch (error.Number)
                {
                    //unique index violation
                    case 2601:

                    //not null violation
                    case 515:

                    //invalid table/view column (column does not exist, either in the view or the underlying table)
                    case 207:

                    //invalid table/view (object does not exist)
                    case 208:

                    //No permission to perform action (SELECT, EXECUTE, etc)
                    case 229:

                    //procedure does not exist
                     case 2812:
                        //log and do not retry
                        logger.Error("Query exception '{0}'. {1}", error.Number, toCheck.Message);
                        return true;


                    //database offline/unavailable
                    case 4060:
                    case 18456:

                        //log and retry
                        logger.Error("Query exception '{0}'. {1}", error.Number, toCheck.Message);
                        return true;

                    default:
                        logger.Error(toCheck, "Query exception with error '{0}'. Attempting recovery if we have not already hit the max for this strategy. Exception message: {1}", error.Number, toCheck.Message);
                        return true;
                }
            }

            // all other exceptions will trigger a retry and be logged so we can determine whether they should be added to the exclusion list. 
            logger.Error(toCheck, "Query exception, attempting recovery if we have not already hit the max for this strategy. Exception message: {0}", toCheck.Message);
            return true;
        }
    }

A partial class to extend the DataAccessAdapter class.


  public partial class DataAccessAdapter
    {
         protected override RecoveryStrategyBase CreateRecoveryStrategyToUse()
        {
            var maxTotalDelay = new TimeSpan(0, 0, 30);
            var maxTotalRetries = 3;
            var delayCalculation = new RecoveryDelay(maxTotalDelay, 2, RecoveryStrategyDelayType.Exponential);
            return new RetryRecoveryStrategy(maxTotalRetries, delayCalculation);            
        }
   }

One thing I'd like to accomplish, but don't know how, is to include the retry count number in the logging. How can I capture that number?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-Oct-2016 09:53:37   

jovball wrote:

One thing I'd like to accomplish, but don't know how, is to include the retry count number in the logging. How can I capture that number?

How about your IsTransientException override? You can keep a count variable in your transient class, initialize it in the constructor and update it in your IsTransientException override.

David Elizondo | LLBLGen Support Team