rebuilding indexes

  • 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.

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

  • Check if there are blockings.

    -Roy

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

  • Can you please brief it a little more.

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

    -Roy

  • I see that its blocked by a process 116

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

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

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

    -Roy

  • Hi Roy,

    ERROR: Process ID 116 is not an active process.

  • 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

  • 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