SQL Server Performance

can I stop the re-index process?

Discussion in 'General DBA Questions' started by dba_boston, Mar 12, 2008.

  1. dba_boston Member

    I did a maintenance run this morning, but it last too long, I stoped the job.
    When I check the database, dbcc opentran(dbname)
    it said:
    Oldest active transaction:
    SPID (server process ID) : 68
    UID (user ID) : 1
    Name : reindex
    LSN : (108169:41955:1)
    Start time : Mar 12 2008 9:36:13:423AM
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    How can I stop it?
  2. thomas New Member

    You may have to kill the spid, by running
    kill 68
  3. satya Moderator

    What is the reason for starting and stopping the maintenance job?
    You have to ensure one thing here such an interruption may cause defragmentated on the table, so better to run UPDATE STATISTICS on the tables if you haven't got time to run REINDEX and also run only required indexes not on all the tables.
  4. suniljk7 New Member

    You tried to stop the job because it took long time, but when you stop or kill this process it needs to ROLLBACK the transaction, so again it takes time for that and also chance to defragmentation is there. you will not be able to access the table which will be locked at this time. so the best idea is to plan an off peak time for all you maintenance and let the process finish.

Share This Page