Table reorganisation

[baby_fabrice]

Hi,

I wanted to know how is it possible to do a table reorganisation.

I ask this question because i have read a lot of subject where the brtools is use for this operation but like my DB is MS SQL 2005 SP3 ....i haven't this tool...

Does the reorg have to be done by an MS SQL tool with the MS SQL manager? or in the Tx DB02 maybe?

Other question :

When is it possible to say " this table has to be reorganise" (What is the main paramèter : zise grow ?)

Thanks in advance.

Regards.

[former_member184473 · Accepted solution]

Hello,

Just to complement the previous reply you can also refer to notes [159316|https://service.sap.com/sap/support/notes/159316] and [377563|https://service.sap.com/sap/support/notes/377563], which will give further information about reorganization.

Regards,

Eduardo

[baby_fabrice]

Hi and thx for all your explain.

I think i have enaught element to close the subject witch is interesting.

So thx again.

[Former Member]

Hi BABY Fabrice,

We do 2005 MSSQL reorgs fairly frequently for one of our clients, here is how we go about it during our weekly maintenance window.

1. Before the window we take an online backup of the DB and Transaction logs.

2. We are on a cluster so we make sure that all resources are on the primary, then pause (or outright shutdown) the second node.

3. SAP is stopped.

4. Go into Enterprise Manager

5. Select the database, right-click, choose Properties

6. We switch to simple mode so we don't overwhelm the t-log during the reorg

7. Restart the DB for the change to take effect

8. On the Enterprise Manger expand the DB, then Management and click on Jobs

9. Create a job and under the Steps tab change the type to Transact-SQL Script (TSQL), and the Database field to your database. In the command field the query we use is as follows:

EXEC sp_help TABLE_NAME <---your table

dbcc dbreindex ('TABLE_NAME', ") with no_infomsgs

GO

dbcc updateusage ('SID','TABLE_NAME') <----SID is your instance SID

GO

10. The job will be added to your list in the jobs section mentioned in step 8

11. Right-click on the job and choose Properties, and then edit Step 1, under "On success action" Quit the job reporting success and change the "Output file" to somewhere where the log can write the log to.

12. Right-click on your job and choose Start Job

13. We've experienced various run times, but it's highly dependent on your hardware. On the production system which is relatively new and powerful I've seen 20GB to 60GB an hour. Typically on our hardware if it was a 200GB table, I'd estimate then entire downtime (6 hours) and ask for a two or three more hours.

14. Be aware that if you are tight on space, you have to be mindful of the size of the table you are reorging...you need equivilent disk space while it rebuilds, meaning that if your table is 20GB, you need another 20GB free while performing the reorg. You can reclaim it from the SAN (if applicable) afterwards.

15. Once done and reporting success, after watching Enterprise Manager and the log (the log doesn't update very frequently at all), be sure that before you bring everything back online that you change the Recovery Model back to Full.

I hope this helps you out and good luck.

Regards

[Former Member]

Hey Robert,

I just want to add one option to your procedure. some of the production systems cannot be shutdown for maintainence, it will break their SLA's. you can go for online rebuild of indexes. Its recommended to perform in very low activity time frame.

and moreover, Reorganizing an index is always executed online.

[http://technet.microsoft.com/en-us/library/ms189858.aspx]

[Former Member]

Hi Fabrice

Table reorganize is performed in SQL server management studio. Unfortunately there is no easy GUI solution to reorganize thousands of tables in SAP.

you have run T-SQL scripts in SQL server management studio, to reorganize or rebuild indexes on the tables.

> When is it possible to say " this table has to be reorganise" (What is the main paramèter : zise grow ?)
> .

Microsoft recommends to reorganize indexes on tables if the Fragmentation level is less than 40% and rebuild indexes, if it is more than 40%.

SAP databases usually have higher level of fragmentations due to huge data loads, so it varies depending upon your environment at what % of fragmentation you go for reorg/rebuild. Please see the link below:

Thanks

Mush