Problem
I have setup a scheduled maintenance plan for a server, to do basic maintenance tasks such as checking database integrity, reorganizing index and cleaning up history. It shows "failed" in job history.
Solution
1. Check the error log
The error log doesn' t provide enough information for trouble shooting:
"DBA Maintenance Plan.Subplan_1,Error,,ServerName,DBA Maintenance Plan.Subplan_1,,,The job failed. The Job was invoked by Schedule 17 (DBA Maintenance Plan.Subplan_1). The last step to run was step 1 (Subplan_1).,00:41:08,0,0,,,,0"
2. Revise Maintenance Plan to Enable "Send report to an email recipient"
Start SSMS, double click the maintenance plan, then click the "Reporting and Logging" icon, then check the button on the left of "Send report to an email recipient", and add an Agent operator.
Running the maintenance plan again will generate an email report. In my case, the report includes the following error message:
Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Object: Tables and views
Compact large objects
Task start: 2017-09-03T10:32:03.
Task end: 2017-09-03T10:33:58.
Failed:(-1073548784) Executing the query "ALTER INDEX [Indexname" failed with the following error: "The index "Indexname" (partition 1) on table "tablename" 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."
3. Find the Table and Index across All Databases on the Server & Change Index Property
The above error message lists the name of the table and index name, but not the database name. We can use this query to determine which database they belong to:
sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''tablename'''
Then we can use SSMS to modify the index property, change "Allow page locks" from "False" to "True".
Run the maintenance plan again, and there is no error anymore.
