This post is to group details about one possible cause of failure on SQL Agent Jobs that have Maintenance Plans inside. Because the Job engine is not very clever when storing the messages returned from the SSIS Package (that is the real maintenance plan inside SQL), sometimes it may be hard to diagnose and fix a plan that keeps consistently failing.
It may happen on any maintenance task, but today it happened on a “Reorganize Index Task”. Because the return of the package is toooo verbose, all I could see was the logs from the start of the execution, and not the error message. So I went ahead and edited the Maint-Plan to generate the SQL Script for each step, and started to run this script in batches. The first executions ran fine, but then while running the script from the reorganize task, one error message appeared:
Msg 2552, Level 16, State 1, Line 1 The index "IX_YourIndex" (partition 1) on table "Tb_YourTable" cannot be reorganized because page level locking is disabled.
So, the first culprit was a badly behaving index. I don’t know why it was created this way but ALLOW_PAGE_LOCKS is usually good as it prevents table level locking most of the time. So I recreated the index with this option set to ON. Below some info on this.
From this link: “The allow_page_locks column of sys.indexes view may help you find indexes having page level locking disabled”. Once this was changed, the package could not fail anymore.
OR COULD IT???
Yes it could, and it did. Index fixed, job started, job failed. Again no useful error messages logged. The next task inside the maintenance package was a “Maintenance Cleanup Task”. When I edited it the second culprit became quickly obvious: the folder that the task was trying to clean was renamed. It could not find the path, failed, and refused to log anything on the SQL Agent logs. Folder name fixed, job started, job SUCCESSFUL!
Thats it. Diagnosing maintenance plans in SQL Server is not exactly easy as it should be, this is one of the reasons I prefer custom-made jobs with T-SQL scripts doing specific tasks, but they are certainly a lot faster and easier to implement on simpler environments.
Thats all for today. Cya!