MSSQL Database Customization

By default, MSSQL databases are not configured optimally, which means that deadlocks can occur in the database when there are many concurrent and long-running transactions—such as those performed automatically by processes. This may occur more frequently, depending on the process. For this reason, it is recommended to update the MSSQL database on older portals as a precautionary measure.

In processes with very long execution times, even a few accesses to the same data set can cause errors due to deadlocks in the database. With the following adjustment, all deadlocks can be avoided, and the process can be used simultaneously by multiple users or actions.

As a general rule, please check whether your process can be further optimized.

The following switch is used to control how data is read:

"Read operations at the read-committed isolation level are based on snapshot scans and do not acquire locks."

First, check the current status. To do this, you can run the following SQL command:

            select name, is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc from sys.databases

To enable the settings, execute the following SQL command.

It is recommended that you shut down the Intrexx portal service and all other systems with access to the database during the query in order to close all connections to the database. As long as connections to the database are open, the command waits and therefore cannot be completed.

Command for adjustment:

            ALTER DATABASE <DATENBANKNAME> SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE <DATENBANKNAME> SET READ_COMMITTED_SNAPSHOT ON

Please follow these steps when making adjustments:

  • Close all Intrexx portals that access the database.

  • Terminate any connections from external systems that are accessing the database.

  • In SQL Server (SQL Server Management Studio), check whether there are any open connections to the database. These must be stopped.

  • Execute the database command.

  • Check to see if the database command was executed.

  • Start the systems.

More Information

https://msdn.microsoft.com/de-de/library/tcbchxcb(v=vs.110).aspx