Post reply

Maintenance plan failing on index rebuild: deadlock victim

  • November 29, 2011 at 11:27 am

    #248742

    During the step of the maintenance plan when the indexes are being rebuilt, the task fails. I get this following message in the logs:

    Failed:(-1073548784) Executing the query "ALTER INDEX [PK_tblDrivers] ON [dbo].[tblDrivers] ..." failed with the following error: "Transaction (Process ID 93) 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.

    I ran lock traces and found the sql that was conflicting with the plan. They were both set to deadlock priority 0 (normal), as I'm sure is the default, and the index rebuild fails.

    I don't have a way to cancel the other conflicting sql. I think I need to find a way to raise the deadlock priority on the maintenance plan.

    So, I thought I'd be clever and added this step at before all of the others to my job:

    SET DEADLOCK_PRIORITY HIGH

    GO

    This had no effect.

    Can someone please help with either changing the deadlock priority or finding another solution?

    Thanks

    Running SQL Server 2008 SR2

  • November 29, 2011 at 11:33 am

    #1414269

    Start traceflag 1222. Then when it happens again, go to the sql error log and paste the deadlock graph over here.

    In the meantime, you can use this instead of the MP. It's much more lightweight than rebuilding everything.

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

  • November 29, 2011 at 2:07 pm

    #1414342

    Here's the deadlock graph. Do you need anything else?

  • November 29, 2011 at 2:42 pm

    #1414362

    No we need the xml version, this is totally useless without the GUI 😉

  • November 30, 2011 at 4:27 pm

    #1414982

    I was wondering what useful info you'd get from that. Ha!

    Trace File

    Here's the xml file exported from my trace. How's that? I included all of the Lock related entries.

  • November 30, 2011 at 4:32 pm

    #1414983

    How do you do the reindex?

  • December 1, 2011 at 7:37 am

    #1415262

    I just picked it from the maintenance plan wizard. The Tsql is auto-generated.

  • December 1, 2011 at 7:39 am

    #1415263

    This is a way better method of going about this.

    Ninja's_RGR'us (11/29/2011)

    Start traceflag 1222. Then when it happens again, go to the sql error log and paste the deadlock graph over here.

    In the meantime, you can use this instead of the MP. It's much more lightweight than rebuilding everything.

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

  • December 1, 2011 at 7:52 am

    #1415275

    I figured that rebuilding would make for cleaner indexes. Is this not the case? Does the script you're linking me to do a good enough job without rebuilding?

  • December 1, 2011 at 7:56 am

    #1415285

    Did you read the blog post?

    It does about 100 different things, all better than whatever the MP is doing.

  • December 1, 2011 at 7:59 am

    #1415290

    I skimmed through it because it's a bit beyond my current level of knowledge. I will roll up my sleeves and dive in.

    Thanks for the advice!

Viewing 11 posts - 1 through 11 (of 11 total)

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