LS Retail - Data Director - SQL Timeout, Failed to Insert, Failed to Update

• Causes
• The DBFileSize is too small, and SQL is taking too long to resize itself.
• DD gives up and rolls back
• SQL gives up and doesn't resize
• Infinite loop of trying and failing
• Multiple jobs accessing the same table cause table locks
• Ensure you only have one job per table running at a time
• If a large job fails, it will send a rollback request to SQL which takes forever to execute
• This will generally lock up the table and prevent any further transactions for an extremely long time
• You cannot kill the rollback process
• If you restart the service, the database goes into recovery mode until it clears the rollback, and will then be available again
• DO NOT Increase Data File Size in DD Options
• Controls size of data file generated on local drive
• Max is around 2gb
• DO NOT Increase Message Size in DD Options
• Controls the number of records that can be sent in a single job
• Max is around 2million records
• 
  
• Solution
• Manually resize the database file size by going to properties>file size> Set it to something larger that will not resize constantly (+20gb)
• Use the table filters to reduce the records to around 2 million, or 2gb