cancel
Showing results for 
Search instead for 
Did you mean: 

DB reorganization on SQL 2005

Former Member
0 Kudos

Our database is becoming quite large and we were told that it may be possible to reorganize the database in order to free up space and make the database run more efficiently. Would anyone be able to provide any documentation regarding this procedure? I have not been able to find anything on SDN or the through SAP notes. Any help would be appreciated!

Accepted Solutions (1)

Accepted Solutions (1)

former_member182307
Contributor
0 Kudos

Hello Connie,

A few years ago I used an SQL script for sql server inspired by :

http://sqlfool.com/2009/06/index-defrag-script-v30/

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

You really should have a look at it.

I remember i had to adapt it a little especially in the way it dealt with the LOBs in order to be able to perform the defrag online.

Still, even if we talk about "online" defrag, you should run it on a low database activity timeframe.

I remember applying this rule :

If an index is 5-30% fragmented, reorganize the index.

If an index is over 30% fragmented, rebuild the index.

I was able to save around 200Gb on a 600Gb database.

It depends on how often the DB is defragmented of course.

Hope this helps.

Regards,

Steve.

Former Member
0 Kudos

We can't really expand the drives at this time since we're trying to put in cost saving measures at this time. Is there any documentation stating that SAP does not recommend a database reorganization?

Former Member
0 Kudos

If it is for cost saving, just upgrade your database (to SQL 2008/R2) with minimum investment and get huge savings on storage by compressing your database. We got 85% of compression ratio on our BI landscape.

It gives you the long term savings on your investment.

Regards,

Nick Loy

former_member182307
Contributor
0 Kudos

Hello,

I don't really get why would SAP be against DB reorg/defrag in order to free space on the file system.

SAP does not recommend it for SQL server if you expect some performance optimization.

But in the case you need to free some space on the file system, the reorg/defrag and shrink of the db should be performed.

Here are a few notes where you' ll read about both point of views :

1721843  - SQL Server: Database-related post-processing after freeing a significant amount of space

Note 159316 - Reorganizing tables on SQL Server

I agree to the fact that the DB will be fragged again later but on my past experience, the defrag of our entire landscape led to around 1Tb of space savings and allowed us to postpone our storage investment for a at least year.

Best regards,

Steve.

jairo_pedroza
Explorer
0 Kudos

Hello Connie,

Depending how large is your DB, think about using SAP Tools R3load to export and import DB in last DB version. Could be MS SQL 2008 or MS SQL 2012 (will be released in june end).

It could bring huge benefits when we're thinking in saving. Please see below link

http://scn.sap.com/community/sql-server/blog/2012/05/07/sap-ecc-60-running-with-ms-sql-server-2012

Feel free if you have doubts about export/import process

Hope it help you.

Regards,

Jairo Pedroza

Former Member
0 Kudos

Thank you all for your input! I really appreciate it! We are planning on upgrading our SQL databases next year to SQL 2008. From what you mentioned this will help us significantly with our database sizes and performance.

former_member193399
Active Participant
0 Kudos

Make sure you upgrade to 2008 R2. there is a lot improvement with respect to compression between 2008 and 2008 R2.

Also you need to be on certain SP level for the compression to work. Compressing database is a manual task and requires downtime especially for huge tables. Review these notes and also search for MSSCOMPRESS in sap notes. This is the program which does the compression.

https://service.sap.com/sap/support/notes/1459005

https://service.sap.com/sap/support/notes/1488135

Hope this helps!

Answers (1)

Answers (1)

former_member193399
Active Participant
0 Kudos

Why not expand the drives? SAP against db reorganization.

Also first check for faster growing tables in dbacockpit->spaces to make sure any of the maintenance tables are not taking up space. make sure you have all the house keeping jobs running fine.

You can upgrade to SQL 2008 R2 and compress the database. this would free the space upto 50% instantly for unicode database.

If you still want to re-org you can do that. but the database will be back to the current position after some time.

-RT