Database Server issues

From DocWiki

Revision as of 07:42, 24 February 2011

Database Server Configuration

• Verify mode of authentication set in SQL Enterprise Manager (needs to be mixed-mode)
• Verify network packet size
• Verify TCP/IP port (Default port = 1433)

Database Server issues

• High Memory and CPU utilization
• Verify if rebuilding of indexes, archiving jobs, and reports summary jobs are running simultaneously.

Database Performance issue

• Run custom performance trace/trace templates that may have been created by a Database Administrator (DBA) to collect performance times associated with queries on DB.
• Run a trace using SQL Query Profiler, and identify queries that may be taking more than 5 seconds to execute completely.
• Change application log trace level to determine database queries (see Unified EIM / Unified WIM user guides)
• Analyze <Unified EIM / Unified WIM Install Dir>/logs/eGainQueryTimeLog.txt for queries that may be taking more than 10 seconds to execute completely.

DB Restoration issues

• Verify user privileges (user must have DB System Administration role assigned)
• Verify default database for the database users (Active and Master databases).
• Verify location of full text index (FTI) catalogs in file system. Typically, the FTI catalogs reside in the <MS SQL Server>/MSSQL/Data directory.

Database Configuration Best Practices

• Rebuild indexes once per week.
• Verify that ‘Auto-update statistics’ setting is enabled.
• Verify that ‘Auto-create statistics’ setting is enabled.
• Configure and analyze performance monitors.
• Configure appropriate DB size for Unified EIM / Unified WIM Master DB, Active DB and Archive DB based on anticipated DB growth (Recommended size for Active DB is 4 GB, Master DB is 1 GB, and Archive DB is 1 GB. Set the property for database to grow incrementally if required. Please refer Deployment Sizing Guide for Unified EIM / Unified WIM for computing database size based on system parameters).
• Consolidate regular database maintenance plans.

Note: The above best practices apply to both Active (stores data associated with the default application partition) and Master (stores data associated with back-end processes and shared resources) databases.