Symptoms
K2 SQL database backup failed due to index modifications:
The index "PK_Server_Act" on table "Act" cannot be reorganized because page level locking is disabled. .SQLSTATE 42000] (Error 1943)
The index "PK_Server_FieldOnDemand" on table "FieldOnDemand" cannot be reorganized because page level locking is disabled. .SQLSTATE 42000] (Error 1943)
It looks like the system is automatically changing the database to ALLOW_PAGE_LOCKS off. The dates that this happened no one was in the system at the time so we don't know how it was changed.
Diagnoses
You are correct, K2 does disable page level locking. Here is why:
In order to minimize database blocking and deadlocks there are several indexes in the K2 database(s) where page locking is disabled by design and row level locking is used instead. For these indexes, page locking should not be enabled as this could lead to database contention issues at runtime.
In 4.6.x there is a stored procedure (in the K2Server database) called Utility.Maintain_IndexDefrag that will defragment or rebuild the indexes as needed. Now that the K2 database(s) are schematized, you can pass the SchemaName as an input parameter to the stored procedure. For example if you want to run maintenance on the indexes for the tables that correspond to the K2ServerLog database, you can pass "ServerLog" as the schemaname. Usage of this stored procedure can be found in the comments of the stored procedure header.
As documented in the stored procedure, below are the default settings that would determine if the index will be reorganized or defragged:
Important Default Settings:
- @minpagecount = 1000 --> if an index has less than 1000 pages and is in memory, don't bother removing fragmentation
- @MinFragmentation = 5 --> if logical fragmentation is less than 5%, don't do anything
- between 5% - 30% --> reorganize it
- @RebuildThreshold = 30 --> if more than 30% logical fragmentation, rebuild it
It is also usually best practice to:
- backup the database before performing index defragmentation
- rebuild the indexes during scheduled downtime after stopping the K2 service
- familiarize est this stored procedure in another environment prior to application in production
However, if your DBA wants to reorganize only certain indexes (the 'PK_Identity_CacheConfiguration' index in this case) while the K2 Service is NOT running, they can temporarily allow page level locking by following these steps:
1. Stop the K2 Server service
2. Alter the index by setting ALLOW_PAGE_LOCKS = ON
3. Reorganize the index.
4. Alter the index by setting ALLOW_PAGE_LOCKS = OFF
5. Start the K2 Server service
Resolution
Which method you use to perform index maintenance is up to you. Our OOB stored procedure is built to simplify the task for you. If you prefer to reorganize only specific indexes (rather than all indexes for a single schema), then your own procedures would be required.
The reason the K2 service needs to be stopped is so that you can temporarily turn "ALLOW_PAGE_LOCKS" to "ON", if reorganizing only a specific index. This is to prevent database contention issues while page level locking is enabled.
If you use the built-in stored procedure, you do not need to stop the K2 service first, but it is recommended as a best practice.
As far as how frequently to run the job, that is going to depend on your usage and how frequently the fragmentation becomes an issue. We do not have a single specific recommendation for this.
