We have the audit logs set to go to MS SQL Server. In the last couple of months we have seen an increased number of connections that don't ever commit the transaction.
When the issue is present I can see the following error in the SiteMinder Policy Server log:
"Exception occurred while executing audit log Bulk insert"
Profiler shows the following error:
[08/31/2008][01:05:45.176][CSmDbConnectionODBC::MapResult][State= HYT00 Internal Code = 0 - [Microsoft][ODBC SQL
Server Driver]Timeout expired][[Microsoft][ODBC SQL Server Driver]Timeout expired]
[08/31/2008][01:05:45.176][CSmDbConnectionODBC::MapResult][Mapped Result: -4007 Error Message: "[Microsoft][ODBC SQL
Server Driver]Timeout expired" SQL State: HYT00.]
We need to understand why SiteMinder is leaving open connections to the audit database with uncommitted transactions and how to fix it.
Profiler shows that the Driver is closing the connection due to timeout and therefore you can expect uncommitted transactions in your Database.
In SiteMinder 6 a "Bulk insert" mechanism was introduced in order to speed-up the audit process when the audit mode has been set to Asynchronous. This mechanism also disables the "auto commit" option of the driver.
The issue is because SiteMinder is trying to insert data in the audit store, the insert is taking too much time (more than 15 seconds) and the driver is timing out the connection. As result the connection to the database is broken leaving the transactions in uncommitted state.
There are 2 options to fix the problem:
- Find out if there's something blocking the audit table or if there's any process causing a performance problem in the database.
- Increase the values of registry keys QueryTimeout and ConnectionHangwaitTime in the sm.registry file. This will allow long inserts to finish. Please note that these registry keys are only available in SiteMinder Policy Server 6 SP5 and Higher.
For more reference please check Policy Server Management Guide Chapter 3: "Configuring Policy Server Data Storage Options" - Configure a Separate Database for the Audit Logs - Audit Logging Database Type Considerations