Microsoft Axapta uses more memory than usual and SPID connections may become orphaned

Article ID: 916243
Microsoft Business Solutions-Axapta 3.0 is now part of Microsoft Dynamics AX 3.0. All references to Microsoft Business Solutions-Axapta and Microsoft Axapta pertain to Microsoft Dynamics AX.
Expand all | Collapse all

On This Page

SYMPTOMS

In a three-tier Microsoft Business Solutions - Axapta 3.0 implementation, you kill Microsoft SQL Server 2000 process ID (SPID) database connections. Alternatively, you end the Microsoft Axapta client process (Ax32.exe). When you do this, you may experience the symptoms that are described in the following scenarios.

Scenario 1

Connections between the client and SQL Server are managed by the Axapta Object Server (AOS). The AOS opens a connection to SQL Server on behalf of the client. A user starts a long-running query, such as an ad hoc report in which no selection criteria is selected. After the query process starts, you end the Microsoft Axapta client process by using Task Manager. However, the AOS still has a connection open to SQL Server, and SQL Server is still processing the request.

In this scenario, the connection between the client and the AOS is broken. However, the connection between SQL Server and the AOS is still active.

In SQL Server, the query must finish before SQL Server verifies whether the connection is still active. SQL Server then returns results to the AOS. However, the AOS has no active client to which to return the results.

In this scenario, you experience the following symptoms:
  • The Microsoft Axapta server process (Ax32serv.exe) may use more memory than usual.
  • SPID connections may become orphaned.
  • The following events are logged in the Application log:

    Event Type: Warning
    Event Source: Axapta Object Server
    Event Category: None
    Event ID: 181
    Date: 4/13/2006
    Time: 2:23:28 PM
    Computer: ComputerName
    Description: Object Server Axapta4: AOCP Connection error: Client thread 0x1260 for session 2(Admin) canceled.

    Event Type: Error
    Event Source: Axapta Object Server
    Event Category: None
    Event ID: 136
    Date: 9/16/2005
    Time: 11:21:11 AM
    Computer: ComputerName
    Description: Object Server Axapta: User Username (wait w/o response in session 40, thread 0x668) disconnected abnormally

Scenario 2

In a Microsoft Axapta three-tier thin configuration, three-tier fat configuration, and two-tier configuration, the connections between the client and SQL Server are managed by the AOS. The AOS opens a connection to SQL Server on behalf of the client. A user starts a long-running query, such as an ad hoc report in which no selection criteria is selected. After the query process starts, the SQL Server administrator views the Sysprocesses system table, or uses a stored procedure that monitors and kills orphaned SPID connections. The administrator notices a SPID that has had a long batch time. The administrator notices that most SPIDS that are related to Microsoft Axapta have a status of "Sleeping" and "Waiting Command," even when the Microsoft Axapta processes are running. This behavior is typical for Microsoft Axapta. For more information about SPID blocking problems in SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems

If the SQL administrator or the stored procedure kills the SPID that has been identified as orphaned, the connections between the AOS and SQL Server are flushed by stopping and then restarting the AOS. This process closes the connections. However, SQL Server is still working on the request it was given, and does not stop. The administrator notices that the SPID is still active, and then kills the SPID connection in SQL Server.

In this scenario, you experience the following symptoms:
  • When the SPID is killed, the specific connection is lost. Alternatively, the cursor is lost for the session with the AOS.

    The AOS cannot manage the open cursors that are associated with the killed SPIDs. Memory usage on the AOS then remains elevated because of the abnormal session tear down, whereas the SPID is typically left "dirty."

    Note A database is "dirty" when the database has major changes that were made to it since the last time the changes were checkpointed or written to disk.
  • Communication with the AOS session connection pool is lost.

    This occurs because when the SPID is killed in SQL Server, the session at the server's level is killed.
  • The following event is logged in the Application log:

    Event Type: Error
    Event Source: Axapta Object Server
    Event Category: None
    Event ID: 117
    Date: 9/16/2005
    Time: 12:34:40 PM
    Computer: ComputerName
    Description: Object Server Axapta: The database reported (session 18 (mwint)): [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).. The SQL statement was: "SELECT A.USERID,A.RECORDTYPE,A.ELEMENTNAME,A.DESIGNNAME,A.ISKERNEL,A.COMPANY,A.RECID,A.VALUE FROM SYSLASTVALUE A(INDEX(I_65528USERID) NOLOCK) WHERE ((((((RECORDTYPE=?) AND (ELEMENTNAME=?)) AND (USERID=?)) AND (ISKERNEL=?)) AND (DESIGNNAME=?)) AND (COMPANY=?)) OPTION(FAST 2)"

CAUSE

Scenario 1

This issue occurs when the user ends the Microsoft Axapta client process by using Task Manager.

Scenario 2

This issue occurs if the SQL Server administrator or the stored procedure issues a KILL command to remove the SPID that has been identified as orphaned, and that SPID remains active in SQL Server.

RESOLUTION

Scenario 1

To resolve the issue that is described in Scenario 1, use the following methods:
  • Confirm the reason that the SQL connections remain open, and isolate the process that causes open connections.
  • Verify whether SQL SPIDs are being blocked, and isolate the process that blocks the SPIDs.
  • Let Microsoft Axapta close sessions whenever possible by logging out of the Microsoft Axapta client to maintain memory allocation. Do this instead of hard-killing a Microsoft Axapta client session. Memory usage results from the number of open and closed cursors that are used by Microsoft Axapta.
  • Do not close SPID connections by using Task Manager. This process does not correctly release the cursor memory on the AOS. Instead, you should first try to end the user session by using the Microsoft Axapta Online Users form. If you must close the SPID connection, you should restart AOS during off-hours. Alternatively, schedule downtime to recover cursor memory.
  • Set the idle connection time-out in the Microsoft Axapta Configuration Utility. To do this, follow these steps:
    1. Click Start, point to All Programs, and then click Microsoft Axapta Configuration Utility.
    2. Click the Database tab.
    3. Click Time out connection after being idle for. In the Seconds box, enter the number of seconds that you want to elapse before a background thread releases any inactive database connections.

      Note By default, the idle connection time-out value is set to infinity. Therefore, when a database session is created in the connection pool, the session is never released.
  • Set the number of retries that you want to occur when you create client connections. Do this if you are experiencing connection problems because of network failures. To do this, follow these steps:
    1. In the Microsoft Axapta Configuration Utility, click the Database tab.
    2. Increase the value in the Number of retries when creating connection box. This can help make the AOS more resistant to network problems.
  • Increase the time that you want to elapse before a failed client connection is retried. To do this, follow these steps:
    1. In the Microsoft Axapta Configuration Utility, click the Database tab.
    2. In the Retry delay when creating connection box, enter the time, in milliseconds, that you want. This can help reduce memory usage when the database is under heavy usage.
  • For more information, see the "orphaned session" topic in SQL Server Books Online.

Scenario 2

To resolve the issue that is described in Scenario 2, use the following methods:
  • Confirm the reason that the SQL connections remain open, and isolate the process that causes open connections.
  • Verify whether SQL SPIDs are being blocked, and isolate the process that blocks the SPIDs.
  • Let Microsoft Axapta close sessions whenever possible by logging out of the Microsoft Axapta client to maintain memory allocation. Do this instead of hard-killing a Microsoft Axapta client session. Memory usage results from the number of open and closed cursors that are used by Microsoft Axapta.
  • Do not close SPID connections manually or by using a "SPID killer" script. This process does not correctly release the cursor memory on the Microsoft Axapta client process. Instead, you should first try to end the user session by using the Microsoft Axapta Online Users form. If you must close the SPID connection, you should restart the AOS during off-hours. Alternatively, schedule downtime to recover cursor memory.
  • Set the idle connection time-out in the Microsoft Axapta Configuration Utility. To do this, follow these steps:
    1. Click Start, point to All Programs, and then click Microsoft Axapta Configuration Utility.
    2. Click the Database tab.
    3. Click Time out connection after being idle for. In the Seconds box, enter the number of seconds that you want to elapse before a background thread releases any inactive database connections.

      Note By default, the idle connection time-out value is set to infinity. Therefore, when a database session is created in the connection pool, the session is never released.
  • Set the number of retries that you want to occur when you create client connections. Do this if you are experiencing connection problems because of network failures. To do this, follow these steps:
    1. In the Microsoft Axapta Configuration Utility, click the Database tab.
    2. Increase the value in the Number of retries when creating connection box. This can help make the AOS more resistant to network problems.
  • Increase the time that you want to elapse before a failed client connection is retried. To do this, follow these steps:
    1. In the Microsoft Axapta Configuration Utility, click the Database tab.
    2. In the Retry delay when creating connection box, enter the time, in milliseconds, that you want. This can help reduce memory usage when the database is under heavy usage.

MORE INFORMATION

Communication between the AOS and SQL Server occurs by using session objects. The session object is created from a cursor object. The session object acquires a session ID from the AOS connection pool. An AOS cursor is used as a record pointer that holds the current location in a record set from a SQL Server table. The cursor fetch that is observed in SQL Profiler (sp_cursor fetch) is the Microsoft Axapta cursor fetch record that is set from the Microsoft Axapta database. The session information for SQL Server is received from the AOS connection pool. The AOS connection pool retains the session ID for reuse. SQL Server communication is one-way, and occurs only from the AOS to SQL Server. Communication from SQL Server to the AOS goes through the session object. You can find the total number of SQL cursors by combining the number of cached and active SQL cursors.

Properties

Article ID: 916243 - Last Review: July 28, 2011 - Revision: 2.0
Keywords: 
kbmbsadministration kbtshoot kbmbsmigrate kbprb KB916243

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com