Post reply

rebuilding indexes

  • October 23, 2009 at 6:36 am

    #134704

    Hi I am trying to rebuild indexes on a database and its taking forever .I am unable to debug

    the issue. Before 2 months it usually takes around 47 min by my past 2 attempts are failure.

    Last time it took more than a day so I got to abort the job an at yesterday I initaited the job at 11.00pm an I see its still running.

    can you please let me know how to solve this problem???????I would appreciate your response...........

    I am in a bit of crises..........Performance is bad.

  • October 23, 2009 at 6:39 am

    #1069956

    Have the indexes been created with the ONLINE option set to on?

  • October 23, 2009 at 6:43 am

    #1069962

    Check if there are blockings.

    -Roy

  • October 23, 2009 at 6:43 am

    #1069963

    Yes I am basically using a script ,so it is .

  • October 23, 2009 at 6:46 am

    #1069968

    Can you please brief it a little more.

  • October 23, 2009 at 6:56 am

    #1069979

    You can run sp_who2. You will the SPIDS that are blocking

    -Roy

  • October 23, 2009 at 7:07 am

    #1069987

    I see that its blocked by a process 116

    unable to kill the process from activity monitor, any suggestions?

  • October 23, 2009 at 7:13 am

    #1069991

    What's the error message? Have you tried through query analyser "KILL 116" and seeing what message you get back?

  • October 23, 2009 at 7:15 am

    #1069994

    Can you also provide the script that you used to recreate the index?

    -Roy

  • October 23, 2009 at 7:34 am

    #1070016

    Hi Roy,

    ERROR: Process ID 116 is not an active process.

  • October 23, 2009 at 7:37 am

    #1070018

    That means that by the time you went to kill it 116 was already finished. How many rows does the table have? When you run the command sp_who2, what is the status of the create index spid? Also please provide the script that you used for recreating index.

    -Roy

  • October 23, 2009 at 11:12 am

    #1070184

    After being bitten by the blocking bug in the past where the index rebuild jobs were blocked by a connection from the application that wasn't closed, I implemented a standard practice to include a lock time out in my script that rebuilds the indexes: SET LOCK_TIMEOUT 900000; -- Set it to 15 minutes to avoid causing blocking

    So now instead of the index rebuild job being blocked, it just times out after 15 minutes and moves on to the next index.

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

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