Error COOSTD-00118: The database option READ_COMMITTED_SNAPSHOT is disabled.
First published: 10 December 2024 (cf)
Summary
Using Fabasoft Folio 2025 or above with Microsoft SQL Server, you may receive an Event Log message COOSTD-00118: The database option READ_COMMITTED_SNAPSHOT is disabled.
This is due the Microsoft SQL Server database isolation level READ_COMMITTED_SNAPSHOT is mandatory starting with Fabasoft Folio 2025.
Information
The database isolation level defines, how data in concurrent transactions are isolated from each other. The database isolation level prevents conflicts and dirty reads on parallel transactions.
For consistency, starting with Fabasoft Folio 2025, the Fabasoft Backend services expect the database isolation level READ_COMMITTED_SNAPSHOT, that is regression tested in the development cycle.
This isolation level also prevents the SQL Server error
Error: Invalid Query. Your query resulted in the following error message: "Error 1205: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
Solution
To validate the current isolation level:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE database_id = DB_ID();
returns 1 (enabled) or 0 (disabled) for the current database.
SELECT name, is_read_committed_snapshot_on FROM sys.databases;
lists all databases with the READ_COMMITTED_SNAPSHOT option 1 (enabled) or 0 (disabled).
ALTER DATABASE [database] SET READ_COMMITTED_SNAPSHOT ON;
enables the READ_COMMITTED_SNAPSHOT for the database [database].
Hint: Enable this option for all Fabasoft databases, also on distributed database servers.
Links
Microsoft information about READ_COMMITED_SNAPSHOT
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver16#read_committed_snapshot--on--off-
Advanced information about isolation levels
https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server
Applies to
- Fabasoft Folio 2025 and above
- Fabasoft eGov-Suite 2025 and above