SQL Server Performance Forum – Threads Archive
Database Reorganization – cancelled
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 advancei 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
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.
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.
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]
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]
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.
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.
Whatever it is 2000 or 2005 you could use the above link for best practices with regard to the index optimization.
]]>