How to fix OPTIMIZE_FOR_SEQUENTIAL_KEY Error During Geobank Upgrade

1. Summary

This document outlines the cause and resolution for the error "OPTIMIZE_FOR_SEQUENTIAL_KEY is not a recognised CREATE TABLE option" encountered during Geobank upgrades to version 2025 and above. The issue is related to SQL Server database compatibility levels.

2. Issue Description

Error Message: … OPTIMIZE_FOR_SEQUENTIAL_KEY is not a recognised CREATE TABLE option.

Context: This error occurs during the database upgrade process when upgrading to Geobank 2025 or later versions. It specifically appears when the upgrade script attempts to create or alter tables (e.g., DA_CHANGE_LOG) using the OPTIMIZE_FOR_SEQUENTIAL_KEY option.

3. Cause

The OPTIMIZE_FOR_SEQUENTIAL_KEY table option was introduced in SQL Server 2019 (compatibility level 150). If the Geobank database is hosted on a SQL Server 2019 instance (or newer) but is operating at an older compatibility level (e.g., SQL Server 2017 - level 140, or an earlier version), the database engine does not recognise this newer option, resulting in the error.

Geobank 2025 and later versions likely utilise this feature for performance optimisation on certain tables.

4. Resolution

The primary solution is to ensure that the Geobank databases (both configuration and data) are set to an appropriate compatibility level.

• For SQL Server 2019, the compatibility level should be 150.
• For SQL Server 2022, the compatibility level should be 160.

Steps to Resolve:

4.1. Check Current Compatibility Level

Execute the following T-SQL query in Microsoft SQL Server Management Studio (SSMS) to check the current compatibility level of your Geobank databases. Replace 'YOUR_GEOBANK_DATA_DB' and 'YOUR_GEOBANK_CONFIG_DB' with your actual database names.

SELECT
    name AS [Database Name],
    compatibility_level
FROM
    sys.databases
WHERE
    name IN ('YOUR_GEOBANK_DATA_DB', 'YOUR_GEOBANK_CONFIG_DB');

4.2. Change Compatibility Level

Method 1: Using T-SQL

Execute the following ALTER DATABASE commands in SSMS. Adjust the database names and the COMPATIBILITY_LEVEL value as per your SQL Server version (150 for SQL Server 2019, 160 for SQL Server 2022).

-- For SQL Server 2019 (Compatibility Level 150)

ALTER DATABASE [YOUR_GEOBANK_DATA_DB] SET COMPATIBILITY_LEVEL = 150;
ALTER DATABASE [YOUR_GEOBANK_CONFIG_DB] SET COMPATIBILITY_LEVEL = 150;

-- For SQL Server 2022 (Compatibility Level 160)

ALTER DATABASE [YOUR_GEOBANK_DATA_DB] SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE [YOUR_GEOBANK_CONFIG_DB] SET COMPATIBILITY_LEVEL = 160;

Method 2: Using SSMS GUI

1. In SQL Server Management Studio (SSMS), open Object Explorer.
2. Expand the Databases node.
3. Right-click on your Geobank database.
4. Select Properties.
5. In the Database Properties window, select the Options page from the left pane.
6. Find the Compatibility level dropdown list.
7. Select the appropriate level:
   • SQL Server 2019 (150)
   • SQL Server 2022 (160)
8. Click OK.
9. Repeat these steps for the other Geobank database (e.g., …_CONFIG).
    
   

4.3. Retry Geobank Upgrade

After changing the compatibility level for all relevant Geobank databases, attempt the Geobank upgrade process again.

5. Important Notes

• Ensure you have appropriate permissions (e.g., ALTER DATABASE) to change database compatibility levels.
• It's recommended to perform database changes during a maintenance window or after taking a full backup of the databases.
• The OPTIMIZE_FOR_SEQUENTIAL_KEY feature is designed to "optimize high contention insert workloads by reducing contention on the last page of an index." Ensuring the correct compatibility level allows Geobank to leverage this performance enhancement.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access