MSSQL database customization

The MSSQL databases are not optimally configured by default, so that many simultaneous and long-lasting transactions, e.g. those carried out automatically by processes, can lead to deadlocks in the database. This can occur more frequently depending on the process. It is therefore advisable to adapt the MSSQL database for older portals as a preventive measure.

For processes with a very long execution time, even a few accesses to the same data group can cause errors due to deadlocks in the database. With the following adjustment, all deadlocks can be avoided and the process can be used by several users or actions at the same time.

Please always check whether your process can be additionally optimized.

The following switch can be used to influence the reading behavior of data:

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

First check the current status. The following SQL command can be issued for this purpose:

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

To activate the settings accordingly, issue the following SQL command.

It is recommended to close 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 customization:

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

Please proceed as follows for the adjustment:

  • Close all Intrexx portals that access the database.

  • Terminate all possible access from external systems that access the database.

  • Check in SQL Server (SQL Server Management Studio) whether there are open connections on the database. These must be ended.

  • Cancel the database command.

  • Check whether the database command has been accepted.

  • Start the systems.

More information

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