Troubleshooting Steps for Database related issue

Troubleshooting steps for database-related issues, including jobs getting stuck in 'in-progress' or 'reinitialized' state, transaction logs getting full, data collector unable to access Snow URL, errors during product upgrade, and tickets stuck in 'picked for parsing' status. The probable root causes and steps to resolve each issue are provided.

All Job are stuck ‘in-progress’/ ‘Reinitialized’

Table 1. Table 128 – All jobs are in reinitialized state: Scenario 1
Issue Description All Jobs are stuck ‘inprogress/Reinitialized’ state frequently
Modules Impacted Data collector, Recommendation, iParse, Generic, RBA, Release
Probable Root Cause Database space become full
Figure 1. Figure 164 – Jobs in Reinitialized state
image

Steps to resolve

  1. Check the space in all drives of database
  2. If the drive space is full, then clear the logs/ add additional space on drive (Needed)

Transaction logs getting full

Table 128 – All jobs are in reinitialized state: Scenario 1

Issue Description Transaction logs getting full due to that drives taking too much space
Modules Impacted All module
Probable Root Cause Database space become full

Steps to resolve

  1. Check the space in Log drives of database (G:\Program Files\Microsoft SQL Server\MSSQL13\MSSQL\Logs)
  2. Check which database logs occupying more space.
  3. Now login to ssms and write click on database that you want to shrink. Go to property.
    Figure 2. Figure 165 - DB Property Recovery model Full to Simple
  4. Now select the Option and Change the recovery model FULL-> Simple and press OK
  5. Now right click on database Task->Shrink->Files , Select file type as log and click ok. Logs are shrinked successfully
    Figure 3. Figure 166 - Change file type (logs)
  6. Now again select database right click and go to the property change the recovery model from Simple to Full.
Figure 4. Figure 167 - Recovery model Full

Data Collector is not able to access Snow URL (Proxy Enabled)

Issue Description Data Collector is not able to access Snow URL(Proxy Enabled)
Modules Impacted Data collector job is failed
Probable Root Cause The issue is with one of the SPs - Product version 6.2

Steps to resolve

  1. If error in Data collector job is as shown below:

Error:{"Content":"","Headers":[],"ContentEncoding":null,"ContentLength":0,"ContentType":null,"ErrorException":{"ClassName":"System.Net.WebException","Message":"Unable to connect to the remote server","Data":null,"InnerException":{"NativeErrorCode":10060,"ClassName":"System.Net.Sockets.SocketException","Message":"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","Data":null,"InnerException":null,"HelpURL":null,"StackTraceString":" at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)\r\n at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":"8\nDoConnect\nSystem, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Net.Sockets.Socket\nVoid DoConnect(System.Net.EndPoint, System.Net.SocketAddress)","HResult":-2147467259,"Source":"System","WatsonBuckets":null},"HelpURL":null,"StackTraceString":" at System.Net.HttpWebRequest.GetResponse()\r\n at RestSharp.Http.g__GetRawResponse|185_1(WebRequest request)\r\n at RestSharp.Http.ExecuteRequest(String httpMethod, Action`1 prepareRequest)","RemoteStackTraceString":null,"RemoteStackIndex":0,"ExceptionMethod":"8\nGetResponse\nSystem, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Net.HttpWebRequest\nSystem.Net.WebResponse GetResponse()","HResult":-2146233079,"Source":"System","WatsonBuckets":null},"ErrorMessage":"Unable to connect to the remote server","ResponseStatus":2,"ResponseUri":null,"Server":null,"StatusCode":0,"StatusDescription":null}

It is related to proxy issue

  1. Now login to SSMS > Expand database > Expand iautomate db > Expand programibility > Expand stored procedures and filter as getenvproxy as shown below.
Figure 5. Figure 168 - Stored procedure with filtered condition
  1. Right click and click on modify, check the stored procedure if there is IF NOT EXISTS is in query for proxy details, remove first four lines from stored procedure and then remove NOT and make it as IF EXISTS then execute it
Figure 6. Figure 169 - Query after modification

4. Now reset the data collector job it will be successful.

Issue while upgrading Product from 6.1 to 6.2

Issue Description Error: 40-could not able to open the connection to SQL server
Modules Impacted All component of Product
Probable Root Cause Issue was with the connection string of customer database
Figure 7. Figure 170 - 6.2 installation issue

Steps to resolve

  1. Make changes to the Product DB script and update the installer.
  2. After that, if there is an issue with the connection string, there could be a migration for the server, and the IP address might got changed in the existing/currently used database, but there could be still customer databases with the old IP address. Due to a conflict in IP addresses, installation might fail. So, after updating the Connection string, the issue should get fixed.

Ticket Stuck in Picked for parsing

Issue Description Ticket stuck at picked for parsing
Modules Impacted RunParsing
Probable Root Cause Entry was missing in a table “JobParameterDetails”
Figure 8. Figure 171 - Picked for Parsing Issue

Steps to resolve

  1. Analyze the log file of Product installer, parsing component, recommend component, Apache logs.
  1. It might be found that new tickets' status is shown as picked for parsing in the Product console. But when checked the parsing input data returned from the DB, it might be found to be None.
  2. After that it might be found that an entry is missing in a table “JobParameterDetails” corresponding to Parse component due to which component is not picking the tickets.
  3. Hence, insert this missing Parameter entry in the table after which the job should work fine.

Adaptive Server is unavailable or does not exist

Table 113 – Adaptive Server is unavailable: Scenario 1

Issue Description

Product python components’ jobs are failing with below error message:

(20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (x.x.x.x)\n')
Modules Impacted iRecommend, iParse, Entity Model, iUnique, iKnowledgeIndexer (Advanced Knowledge Component), iKnowledgeScreen (Advanced Knowledge Component), iKnowledgeSearch (Advanced Knowledge Component), iCrawler, iKnowledgeRating, iScript, iKnowledge
Probable Root Cause Database connection string changed

Steps to resolve

  1. Go to Advanced Configuration > Connectivity > Connection Details Menu.
    Figure 9. Figure 172 - Moving to connection details screen
  2. On Connection Details page, in the Actions Column, click on Edit icon of the desired organization.
    Figure 10. Figure 173 - Editing connection string for the desired organization
  3. Enter the new corrected connection string with instance variable removed.
    Figure 11. Figure 174 - Updating connection string for a desired organization

    Sample existing connection string (with instance name “prod”) :

    server=x.x.x.x/prod;database=DB1234;User id=username;Password=password;Min Pool Size=2;Max Pool Size=1500;Connection Timeout=30;Pooling=True;ConnectRetryCount=0;

    OR

    server=x.x.x.x//prod;database=DB1234;User id=username;Password=password;Min Pool Size=2;Max Pool Size=1500;Connection Timeout=30;Pooling=True;ConnectRetryCount=0;

    Sample new updated connection string to be entered (instance name “prod” removed) :

    server=x.x.x.x;database=DB1234;User id=username;Password=password;Min Pool Size=2;Max Pool Size=1500;Connection Timeout=30;Pooling=True;ConnectRetryCount=0;

    If the old connection string did not contain any instance name (“server=x.x.x.x;”), but it is required, you can also add the same to the connection string and update in the same manner (like “server=x.x.x.x//prod”, where instance name is prod).

  4. Click on Update button. And the problem should be resolved now.