SQL Job with Maintenance Plan misteriously failing

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.

http://www.sqlservercentral.com/Forums/Topic301624-146-1.aspx

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!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s