I am pretty new to SQL and I have this maintenance job that keeps failing and I am not sure how to troubleshoot this. I have troubleshot other job failures with google help to success but this one is eluding me. Any help is appreciated. Here is the log...BTW..is there an easier way to read these logs, or does it just take time and experience?

Date        2/1/2015 4:00:00 AM

Step ID     1
Server      SERVER
Step Name       REBUILD
Duration        04:01:38
Sql Severity        0
Sql Message ID      0
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Progress: 2015-02-01 06:00:01.80 Source: Rebuild Index Task Executing query "ALTER INDEX [DTA_reports_indexcolumn_index2] ON [d...".: 40% complete End Pro... The package execution fa... The step failed.

  • 1
    Check your Windows logs to see a more verbose error message. – LowlyDBA Feb 3 '15 at 20:58
  • 1
    Definitely going to need more info - as suggested please post the more verbose log as to the actual reason why. Secondly, could you check sys.indexes in that database to see if the flag for hypothetical index is on or not? – Sean says Remove Sara Chipps Feb 3 '15 at 23:10
  • @dtaylor, I think it's a maintenance plan , you can also check the log under management->maintenance plan -> your maintenance plan and right click on it and click historyyou will get some lead from there. – Biju jose Feb 5 '15 at 10:03
  • Biju jose.... I did that and came up with this.... – dtaylor1971 Feb 5 '15 at 16:57
  • Executing the query "ALTER INDEX [inx_intrf_queue5] ON [dbo].[intrf_que..." failed with the following error: "Transaction (Process ID 89) 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. – dtaylor1971 Feb 5 '15 at 16:59

Firstly, make sure the account executing the job has sufficient privileges to run alter index. If that fails, then find the index "DTA_reports_indexcolumn_index2" and see if you can rebuild it yourself by hand. If that works, then you may have another process hitting this table at the same time the index maintenance is running. You'll have to look through what else is running around this time.

If rebuilding it by hand doesn't work, then explore what error you get, if you get one. You might have to drop and recreate the index. You might also run dbcc checkdb against the database to ensure that there's no corruption and run dbcc checktable against said table to ensure it's not corrupted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.