on 06-05-2012 8:16 PM
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.