Getting an error when trying to run a Maintenance Plan in SQL for rebuilding indexes and statistics

book

Article ID: 156542

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

Customer followed suggestions on HOWTO8589 "Creating a maintenance plan in SQL Server 2005 or 2008 to optimize database performance" but everytime that he tries to run it or when the schedule runs it, it fails.

However, if you manually run a index rebuild, like mentioned in HOWTO4644 "How can I reorganize or rebuild my Altiris database indexes on a SQL 2005 server for improved performance?", it run just fine without errors.

A similar error message may be present:

Example 1:
Executing the query "ALTER INDEX [PK__RM_Resou__A2B5777D057B5010] ON [d..." failed with the following error: "Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Example 2:
Executing the query "ALTER INDEX [IDX_ProductUninstalled] ON [dbo].[Item] REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Cause

This is an issue with Microsoft SQL Server since it is a functionality (Maintenance Plans) owned by this product

Resolution

It was discovered that multiple Microsoft Patches were not installed on the Microsoft SQL server. In this particular instance, this customer updated to SQL Server 2008 R2 SP1 and the issue was corrected. Now the Maintenence Plan runs just fine in their own schedule.


Applies To

Symantec Management Platform 7.1x
Microsoft SQL Server 2008 R2