Post reply

Rebuild Index Task fails in Maintenance Plan

  • September 23, 2010 at 1:27 am

    #143469

    Hi Friends,

    I am new to SQL Server Administration.

    I have created the Daily Maintenance plan as below and the Rebuild Index task fails once in a week.

    Backup Database Task - All Databases --> Maintenance Cleanup Task - Older than 1 weeks --> Check Database Integrity Task - Include Indexes --> Shrink Database Task - All Databases --> Rebuild Index Task - Object Tables and Views --> Update Statistics Full Scan

    Now once or twice in a week the Rebuild Index Task is fails as below

    Failed:(-1073548784) Executing the query "ALTER INDEX [PK_TestSet_Now] ON [dbo].[TestSet_Now] REBUILD WITH ( 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 66) 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.

    Kindly do let help me on this to get it resolved.

    Regards

    Senthil

  • September 23, 2010 at 2:03 am

    #1226132

    The reindex job is failing because the other process(from other job scheduled) may be causing blocking on the tables\databases.so probably you can reschedule the reindex job.

    Thanks,

    Deepak Sahu

  • September 23, 2010 at 2:23 am

    #1226143

    Thanks Deepak for your speedy update.

    No other Job's are Scheduled in the Maintenance Plan. Also i am attaching the Screenshot of the rebuild index task. Let me know if anything has to be done.

    Thanks

    Senthil

  • September 23, 2010 at 2:40 am

    #1226152

    if any of your application hold an lock while the rebuild index running on. if no other job creating a problem try to create trace only for deadlock to find which process or sp ,query creating an deadlock issuse.

    Regards,

    Subbu

    http://mssqlforum.wordpress.com

    Regards,
    Subbu
    Click here to Get Speedy answer or solution

  • September 23, 2010 at 3:19 am

    #1226163

    Thanks Subbu,

    Could you please instruct me to create a trace for deadlock to find the process.

    Regards

    Senthil

  • September 23, 2010 at 10:19 am

    #1226440

    you can configure them by using an interface

    open the SQL Server profiler > File > NeW Trace

    it will ask you the Credential the server you want to trace upon sucessfull login it will open up the Trace Properties Window , here you need to select the Template TSQL_lock will have the deadlock events you can select and run the trace do not forgot to select the Deadlock chain , will have the detail information about the deadlock.

    Note : Running a trace is resource intensive process. please run this from the different machine don't run them on the Production Servers

    Regards,
    Subbu
    Click here to Get Speedy answer or solution

  • September 24, 2010 at 12:12 am

    #1226720

    Thanks subbu.

    🙂

  • September 24, 2010 at 12:21 am

    #1226723

    Noticed that Everyday my Maintenance Plan log is empty as below when the tasks Succeeded. The Maintenance Plan has the following tasks

    Backup Database Task --> Maintenance Cleanup Task --> Check Database Integrity Task --> Shrink Database Task --> Rebuild Index Task --> Update Statistics Full Scan

    Log:

    ===

    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.1399

    Report was generated on "VERSIONONE-NEW".

    Maintenance Plan: V1MaintenancePlan

    Duration: 00:00:00

    Status: Succeeded.

    Details:

    When the rebuild index task fails the complete log is available, otherwise the log will be empty.

    Please let me know what could be the issue and how to solve this.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply