SQL Server Performance

Database Reorganization - cancelled

Discussion in 'Performance Tuning for DBAs' started by brian_anastasia@manningto, Jun 27, 2007.

  1. Hello,

    We have scheduled a "reorganization" of our SAP database this weekend running under SQL 2000 SP#4, which includes rebuilding the tables indexes (tables and indexes are badly fragmented). My question is, if for any reason we needed to cancel the reorganization prior to it finishing normally, would this cause any problems with the database ? and could we just bring the SAP app. back online once the rollback is completed ? or would we need to restore the database. All database backups will be done prior to the task.

    Thanks in advance
  2. joechang New Member

    i am not sure what point there is to rebuilding indexes on SAP

    reindexing helps with table/index scan, or atleast a significant range scan
    i do not recall that SAP does any of this

    the most important thing for SAP is to get to a full 64-bit OS/SQL
    it is not suited for 32-bit
  3. satya Moderator

    It will have impact on the transaction log too or sometimes that might mismatch the page linkages within the SQL database.http://msmvps.com/blogs/ssqa/archiv...p-2000-index-optimization-best-practices.aspx fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. MohammedU New Member

    If you Cancel the reindex operation, it has to rollback and it depends on the size of the table and work done by the operation but should not cause any issues...
    I do cancel the reindex operation when I am out of the maintenance window... not on SAP db...but never had any issues...

    If indexdefrag operation canclled, it will done immediately ...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. Originally posted by MohammedU

    If you Cancel the reindex operation, it has to rollback and it depends on the size of the table and work done by the operation but should not cause any issues...
    I do cancel the reindex operation when I am out of the maintenance window... not on SAP db...but never had any issues...

    If indexdefrag operation canclled, it will done immediately ...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    [Thanks - that's what I thought would happen]
  6. ajitgadge New Member

    Generally, Rebuild Index is doing in maintanance window time for all table indexes. So in case you cancelled this operation so it should rooled back your those table wwhere index rebuiling is in process and to roll back index is no harm. I never find any problem with Databases when i cancelled Rebuild Index.All you need to check DB health or in case you can do DBCC Checkdb then it is good.
    Index Defrag is Online operaion so you can cancel this anytime.

  7. psu New Member

    Hi,
    I saw an issue, when rebuild of index (CREATE W/ DROP_EXISTING) on table >8milions of rows cancel (rollback) smashed up the transaction log, and database started to be in status Not Recovered. There was an not quite clear trick thathelped us return the database tonormal status (like create new db, stop mssql, replace its files with old files (bot no replace in of files), start mssql), but at least it happen. There is sql2000but at the moment i don't recall the SP #.
    p.
  8. satya Moderator

    Whatever it is 2000 or 2005 you could use the above link for best practices with regard to the index optimization.

Share This Page