Database Space Management - SAP BASIS

The database space in a SAP system is a central resource that must be monitored and taken care of continuously in a productive environment. A database standstill due to lack of space, for example, can lead to data loss. Therefore, it is best practice to monitor the data files and their growth constantly with monitors within the system or other utilities, if available. Such utilities and the indicators to look at depend heavily on the database platform. As an example, we will explain a few features of the utilities designed for an Oracle database.

The data in a SAP system using Oracle as its database are distributed over so called tablespaces, which are logical containers of one or more data files where the actual data are. Therefore, tables and their indexes are objects found in data files within tablespaces. Objects, such as tables and indexes, occupy space allocated by the operating system in chunks denominated "blocks" that are normally 8 KB of size (although this depends on the operating system as well). Groups of consecutive blocks form extents and tables and indexes grow in extents whenever necessary.

There are several utilities within a SAP system to monitor tables, indexes, and tablespaces. At the operating system level, BRCONNECT is the utility (substituting SAPDBA) to perform all kinds of monitoring, backups, reorganization and manipulation of objects in the database for a Database Administrator. From a SAP system, several transactions and monitors are available to check the database.

The Tables and Indexes Monitor

From the main menu choose Tools | Administration | Monitor | Performance | Database | Tables/ Indexes or execute transaction DB02. The data from this monitor is refreshed once a day in a background job that executes a data collector program, but you can refresh the data from here (it may lake a long time, so be careful when choosing this option!).

The Database Monitor for space management

The Database Monitor for space management

In this monitor, you can check statistics available for tablespaccs and tables or indexes. In order to check a brief display of the database history and growth, select Space Statistics from the main monitor under the Database System section. This list will give you an idea of how used the total available space is in the system. Pay special attention to the % Used column. It is possible to have a breakdown of tablespaces and tables or indexes by choosing so in the available pushbuttons, fn addition, you can change the period of time to view this history by month, week or daily basis. The same space statistics are available again for tablespaces and tables and indexes from the main screen under the corresponding sections.

It is best practice to check the utilization of the individual tablespaces. Check Space Statistics under Tablespaces. Pay special attention to the % Used column again, because this is an indicator on how used the space for that tablespace is and you would be able to estimate if it is going to overflow in the next few days or weeks by looking at how quickly it is growing by day, week or month once again. When the space used in a tablespace is reaching certain limits, it is important to extend it with BRCONNECT before it gets full to avoid a database standstill. It is up to you to define a comfortable level of space utilization.

To analyze particular tables or indexes, select Space Statistics under the section Tables/ Indexes. A filter will allow you to use wildcards (*) if you wish to display all tables and indexes in the database or to choose one object.Tables that are reaching a very high number of extents are to be monitored. If a table reaches MAXEXTENTS (that you can define with BRCONNECT), the database will send an error and can stand still.

With Space Critical Objects, you are able to check what tables are particularly growing at a very fast pace and monitor it before anything wrong happens. 'Missing Indexes' will display a list of indexes that are created in the ABAP Dictionary and missing in the database or vice versa. Indexes are important for faster access to data. If they are missing, it can lead to performance problems. Sometimes, a reorganization of the database can make the database "lose" indexes or these can be deleted by the database administrator on purpose or accidentally. If you find missing indexes in your system, check whether these have been deleted on purpose or not, if there were reasons to proceed that way.

Finally, in order to obtain a list of the largest objects in your database, click on Detailed Analysis under Tables/ Indexes. If you use wildcards, you will display all tables and indexes in your database. Sort by size to check which ones are the largest. Pay special attention to their growth and history and analyze if such growth is normal in your business. Certain tables that contain transactional data are expected to grow very rapidly.

However, sometimes, there are mechanisms that the functional teams are able to use to avoid data that is not necessary or to summarize it. Other times, data that is not used, such as old spool logs, can be deleted, if not required by the business. And many times, but only defined by the business and especially legal requirements that depend very specifically in each country, archiving data is an option to reduce the database size, having access to the data in an external storage system.In summary, database space management and monitoring is important to save costs in storage and to avoid performance problems.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status