Blocking situations in a Microsoft SQL Server databasePermanent link for this heading

Last update: 29 May 2020 (cf)

SummaryPermanent link for this heading

Based on the user behaviour and the load on a specific Fabasoft production environment it can be possible that you notice situations in your Microsoft SQL Server database where a long running query blocks other SQL statements. This can lead to blocked threads of the Fabasoft COO service that is using this database and in worst case to a system outage until the long running transaction is finished.

InformationPermanent link for this heading

This behaviour can be triggered by the fact that some SQL statements lock a row or the whole database table (due to lock escalation of Microsoft SQL Server). This means that other queries have to wait until the first statement is finished. Fabasoft Support noticed this behaviour in Fabasoft Folio or Fabasoft eGov-Suite installations with Microsoft SQL Server versions >=2005.

SolutionPermanent link for this heading

Fabasoft recommends identifying and optimizing long running transactions (e.g. with Fabasoft app.telemetry) that are leading to such a situation. Nevertheless there is a possibility to change the isolation level in Microsoft SQL Server to "READ_COMMITTED_SNAPSHOT" to reduce the impact of long running queries. Please find more information about isolation levels on the following Microsoft web pages:

AttentionPermanent link for this heading

All changes to the database presuppose a consistent backup of the whole database and stopped Fabasoft COO-Services. Fabasoft has not made regression tests (especially due to performance and stability) with the lock level described in this article. If you experience problems when testing this lock level in your installation please contact Fabasoft Support.

Applies toPermanent link for this heading

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Microsoft SQL Server 2008 R2

More useful linksPermanent link for this heading

Download PDF

Download PDF