After installing AppInsight on our MSSQL instance hosting Solarwinds, I noticed that one of weekly database maintenance jobs was failing. I checked the logs and saw the following error:
Failed:(-1073548784) Executing the query "ALTER INDEX [I_OrionServers_ServerType_HostName_in..." failed with the following error: "The index "I_OrionServers_ServerType_HostName_incl" on table "OrionServers" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I opened a ticket with Solarwinds Support and was told it was a MSSQL issue and they couldn't help. Understood, but the job ran fine for several months before failing recently. I Googled around and found the fix and it seemed to be little to no impact as it was just a simple setting on the index.
1. Login to your MSSQL server
2. Login to Microsoft SQL Server Management Studio application with sa or administrative equivalent
3. In Object Explorer, expand Databases->SolarWindsOrion->Tables->dbo.OrionServers->Indexes
4. Right-click on the index giving the error, select Properties, select Options
5. Under Locks in the main page, you should see "Allow row locks" and Allow page locks"
6. Click down-arrow to right and change from "False" to "True" where needed
In my case, I had to change "Allow page locks" to "True" on all three indexes under dbo.OrionServers before the job would successfully complete. Now the job runs and the alerts have stopped.
I don't know how this got changed. My guess is some sort of upgrade; either Windows, MSSQL, or Solarwinds. I am still investigating root cause.
I thought I would post this here to help if anyone else experiences this problem.