KB19579: How to troubleshoot problems with MicroStrategy Intelligence Server Statistics logging

Sometimes Strategy Administrators may notice that the Strategy Intelligence Server 9.x suddenly stops logging statistics. That condition can be caused by various factors. Some factors are listed below:

1. Database Server hosting the Statistics/Enterprise Manager Warehouse database is not running.
2. Network problems causing connection failure to the database server.
3. Heavy load on the database causing statistics logging to fail.
4. UserID/Password for the statistics database got modified.
5. Strategy Intelligence Server stopped logging statistics.
6. Database connection from the Strategy Intelligence Server to the statistics database gets disconnected.

While there could be other factors contributing to the failure to log statistics, the following steps/logs can help in identifying the cause of the issue.
 

1. Check StatisticsFailedSQLs.log:
   'StatisticsFailedSQLs.log' file is a text file that captures the Structured Query Language (SQL) statements that are issued against the statistics database. This file is only generated in case of a failure where the Statistics Manager component within Strategy Intelligence Server fails to write to the statistics database. For more information on this log, refer to the following knowledge base document:
   
2. KB19579: What is the 'StatisticsFailedSQLs.log' file and how is it used in MicroStrategy Intelligence Server 9.x?

• Check DSSErrors logs:
  If there are connectivity problems to the Statistics database, such errors are generally logged into the DSSErrors logs. Logs similar to the following are seen in the DSSErrors logs. As seen in the error messages below, 'database classes' errors pointing to network errors/communication errors/login errors are seen. The connection string information containing the DSN name, user ID being used is also seen in these logs.
  

  
  2008-09-24 10:44:45.902-05:00 Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ConnectionWrite (WrapperWrite()). 
  General network error. Check your network documentation. Connection String: DSN=EM_WH;UID=sa;APP=Strategy 8.1 ® Common Files;
  WSID= SERVER1;DATABASE=EM_WH;. SQL Statement: insert into IS_SESSION_STATS (SESSIONID,SERVERID,......
  
  2008-09-24 10:44:45.933-05:00 Execute Query failed. Disconnected. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. 
  Communication link failure Connection String: DSN=EM_WH;UID=sa;APP=Strategy 8.1 ® Common Files;WSID= SERVER1;DATABASE=EM_WH;. 
  SQL Statement: insert into IS_PROJ_SESS_STATS (SESSIONID,SERVERID,.....

  
   
  
• Use ODBC Test Tool to test connectivity to the Statistics database:
  Assuming that the DSN is correctly configured to connect to the Statistics database, try connecting to the Statistics database using ODBC Test Tool from the Intelligence Server machine. If the connection from ODBC Test Tool is not successful, then it shows that the issue is most likely related to the database server not running or some other network problems. Users should check with their database administrators to check if there are any issues with the Database Server.
   
  If the connection from ODBC Test Tool is successful, then the problem is most likely because of incorrect connectivity information in the Statistics database instance. Users should check the definition of the Statistics database instance and make sure that the correct Statistics DSN and corresponding login are chosen.
   
  
• Check database connection monitor:
  In some cases, users notice that there are no errors associated to Statistics logging in the DSSErrors logs. In this case it is useful to check if there is an open/cached database connection from the Intelligence Server to the Statistics database. When the Intelligence Server starts up and when there are projects configured to collect Statistics, a database connection gets spawned to the Statistics database and remains open until the Intelligence Server is stopped. This database connection can be verified from the Database Connection Monitor as shown below.
   
  

• If no connection to the Statistics database is seen, Administrators should restart the Intelligence Server to try to spawn a new connection to the Statistics database.
   
  
• Enable Statistics Logging:
  If there are no errors in the DSSErrors and a connection from the Intelligence Server to the Statistics database is present, the most likely the problem is with the Intelligence Server itself where the server stops trying to log Statistics. To verify if the Intelligence Server is even attempting to log Statistics, the Kernel > Statistics Trace logs will be helpful.
   
  This logging should be enabled under the 'Castor Server Instance' on the Diagnostics Configuration editor, as shown below.
  

• If the server is attempting to write collect/write Statistics, logs similar to the following will be seen in the Statistics Trace:
   
  

  
  2008-09-24 11:23:36.548-05:00 TRACE:MsiStatisticsMgr::WriteStatsToDatabase(iTable=0x03418C40)
  2008-09-24 11:23:36.564-05:00 TRACE:MsiStatisticsMgr::WriteStatsToDatabase(iTable=0x03418CB0)
  2008-09-24 11:23:36.595-05:00 TRACE:MsiStatisticsMgr::ExecuteSql(iSql=insert into IS_PROJ_SESS_STATS (SESSIONID,SERVERID,SERVERNAME,SERVERMACHINE,USERID,
  PROJECTID,CONNECTTIME,DISCONNECTTIME,EVENTNOTES,RECORDTIME) values ('F3AD4585438E6960CF2CEDB1C600C4F9', '3EC3BD354C80D7FB9C8B80A2B480ACBC', ' SERVER1', 
  ' SERVER1:34952', '54F3D26011D2896560009A8E67019608', 'FEC0B07E49ED148E42B0789CF5C4C887', {ts'2008-09-24 11:23:34'}, null, null,GETDATE()))
  2008-09-24 11:23:36.767-05:00 TRACE:MsiStatisticsMgr::WriteStatsToDatabase(iTable=0x03418D20)