Cannot execute [Utility].[Maintain_IndexDefrag], stored procedure Database is not available
kbt137997
PRODUCTIssue
Unable to execute the [Utility].[Maintain_IndexDefrag] stored procedure:Database is not available
Symptoms
An error appears from this condition on the stored procedure:
IF EXISTS(SELECT 1 FROM sys.databases WHERE [database_id] = @dbid AND
([owner_sid] = 0x01 OR [is_read_only] = 1 OR [state] > 0 OR [is_in_standby] = 1 OR [source_database_id] IS NOT NULL))
RAISERROR(N'Database is not available', 15, 50) WITH NOWAIT;
Troubleshooting Steps
- Execute the query below to identify the values assigned to each property:
SELECT
owner_sid,
SUSER_SNAME(owner_sid) as dbowner,
is_read_only,
state,
is_in_standby,
source_database_id
FROM sys.databases where name = 'K2' - If any of the values meets the condition, then the error will appear.
In most cases, the K2 database dbowner is assigned to sa which returns this error. Please change the ownership of the database to any user other than sa (eg. K2 service account) and execute the [Utility].[Maintain_IndexDefrag] stored procedure.