You may want to run the following SQL to see which table(s) are getting so large.
exec sp_MSforeachtable 'select count(*) as nr_of_rows, "?" as tablename from ?'
This will list the row count and table name.
With only 430 daily jobs, the EM database should not be so huge so its worth checking for tables with 1,000's of rows, then investigate if you have an EM configuration setting with the wrong value.
I did what you said and i found the followings suspects table:
- size: 3.4 GB
- rows: 16.7 millions
- index space: 1.8GB
i have not knowledge for working with databases, but as far as i know, i have seen the table listed above. That is the same table name that appear in my excel file(an excel file that is connected to the database to poll statistical information about jobs ended ok, ended not ok, time duration, execution date, etc...). As i can get statistical information from the past 2 years, maybe that information is occupying the database, i really dont know. What can you say about that?
RUNINF_HISTORY should be self maintaining based on parameters set in the CCM.
Check out the value of RunTimeHistoryDays. The default is 90. This contains the number of days that Runinfo history information is retained before being cleaned from the Control-M/EM database.
If you have set this higher, this table will grow until the number of days has passed.
To see how many days of history you actually have you can use the following SQL:
select distinct(ORDER_DATE) from RUNINFO_HISTORY
or if you just want the total number of unique days, try:
select count(distinct ORDER_DATE) from RUNINFO_HISTORY
If the vaule of the CCM paramter is smaller than the number of days of runinfo history, then the table is not being automatically maintained. If you can see no reason for this, I would check you have the latest EM fix pack, and if so, then place a call with support.
Thanks my friend, i did everything you recommend me and it seems that the parameter
RunTimeHistoryDays is set to 1000, and actually the DB only have reached to 580 days. I'll find out how many days the company is interested to keep in the database for statistical purposes and modify the parameter to comply with the needs and at the same time keeping the DB with enough free space. This was a very good answer. Thanks