Knowledgebase Article Number

 360050986553

Description

Published June 29th, 2020, at approximately 3:00 p.m. EST. 
Last updated on June 29th, 2020.

Resolution 

At Bastian Solutions, we continually strive to provide superb customer service at the highest standard, and not only reactive but proactive support. Transparency is a key component in helping achieve this objective and with that in mind, we are providing a full root cause analysis for the disruptions in service on March 14th and 16th, 2020 described below.

Summary:

Friday, June 26th

08:02 AM EST
Ryan Sweeten from Inmar called in reporting an issue with all scanners not being able to "execute sequel commands" and that all boxes scanned showed a SQL error.

08:20 AM EST
Tier 1 analyst (T1) logged into the App Server to investigate.

08:24 AM EST
After looking through the Exacta Export logs, there were several errors found relating to the queries not being able to be executed. 

08:26 AM EST
Once seeing the error that queries that were not be able to be executed, T1 ran multiple simple queries within the database to test it, which ran successfully. 

08:35 AM EST
After finding the errors within the Exacta Export logs, T1 restarted ExactaExportAdapter and ExactaExport services, but received error on scanners: "could not execute batch command, SQL not avaliable".

T1 then stopped and started ExactaExportAdapter and ExactaExport services, but still received the same error as above.

08:50 AM EST
Due to a meeting at 09:00 EST, Ryan advised me to reach out to Johnathon if I had any more questions.

09:05 AM EST
T1 reached out to Johnathon to further troubleshoot the ongoing issue, Johnathon informed T1 that the sorter was full and that it was unable to communicate with anything, and also that he was not able to log into Exacta Portal

09:05 AM EST
While waiting on Johnathon to send in picture of error that popped up on Exacta Portal, T1 pulled the Exacta Mobile logs and found more errors.

09:11 AM EST
Received picture from Johnathon and documented it within the ticket.

09:22 AM EST
T1 escalated to a Tier 2 resource (T2) for assistance.

09:27 AM EST
T1 informed Johnathon than the ticket had been escalated to T2, and that we would call back when we determined what was causing the issues.

09:50 AM EST
T1 found new errors within the Exacta Mobile logs that were also related to SQl/Boolean connections.

10:05 AM EST
T2 found that the T drive, where the tempdb database is held, was at capacity and believed that this could be what was causing the various issues. T2 reached out to a Bastian DBA for assistance with this ticket.

10:14 AM EST
After discussing with one of Bastian's Database Administrators, it was determined that the tempdb database was full due to the "Update Daily Stats for Wave" job getting hung.  Since the tempdb was full, there wasn't enough space for new queries/jobs to run, causing the cascading errors that were being seen on site.

10:43 AM EST
The job that is in question was stopped by Bastian's DBA. T1 then reached back out to Ryan to have him test the system.

11:04 AM EST
After testing the system, it was determined that everything was working properly again except for the sorter. The sorter was then purged by Ryan, and functioned properly once tested again. 

11:23 AM EST
T1 sent Ryan Sweeten an email, as requested as to what happened and what was done to temporarily resolve the issue.

Root Cause:

After Bastian's DBA, along with the support team, determined that the issues were being caused by a full tempdb database, several members of Bastian's software division investigated into why the database filled up.

The "Update Daily Stats for Wave" job was originally created to pre-compute Inmar's dashboard which was intended to lighten the load on the database, so that individual Portal sessions did not pull an intense query. This query (which previously was being ran each time that a user viewed their dashboard) normally runs every five minutes, and takes between 30-90s to complete, but the job that started at 1:30p on 6/25 got hung unexpectedly, and never ended. This caused the tempdb database to slowly fill up as the query kept taking more and more resources, and ultimately is what caused the database to reach capacity. 

Next Steps and Preventive Actions:

Bastian's software team was able to optimize the existing query to greatly improve performance and efficiency, and also updated the schedule of when the query will be run, from every five minutes to ten minutes.  This will lighten the overall load on the database, and will provide the query plenty of time to complete, which should prevent this from happening in the future. 

Our commitment to you:

Bastian Solutions understands the impact of the disruption that occurred and affected operations for your organization. It is our primary objective in providing our clients with superb customer service and we assure you we're taking the required preventative measures to prevent reoccurrence.