Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index Task fails in Maintenance Plan Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 1:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:49 AM
Points: 5, Visits: 14
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
Post #991772
Posted Thursday, September 23, 2010 2:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 24, 2014 11:41 PM
Points: 259, Visits: 157
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
Post #991788
Posted Thursday, September 23, 2010 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:49 AM
Points: 5, Visits: 14
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


  Post Attachments 
Rebuild Index Task.png (51 views, 5.71 KB)
Post #991800
Posted Thursday, September 23, 2010 2:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 25, 2012 6:31 PM
Points: 1,375, Visits: 173
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
Post #991814
Posted Thursday, September 23, 2010 3:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:49 AM
Points: 5, Visits: 14
Thanks Subbu,

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

Regards
Senthil

Post #991832
Posted Thursday, September 23, 2010 10:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 25, 2012 6:31 PM
Points: 1,375, Visits: 173
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
Post #992166
Posted Friday, September 24, 2010 12:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:49 AM
Points: 5, Visits: 14
Thanks subbu.
Post #992496
Posted Friday, September 24, 2010 12:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 2:49 AM
Points: 5, Visits: 14
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.
Post #992499
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse