4 Replies Latest reply on Jan 24, 2012 2:30 PM by elvin NameToUpdate

    CTM-EM Database get full to quick.

      Share This:



      I have a CTM environment where the CTM-EM database is on a SQL server and the CTM-Server database reside on the CTM-Server itself. The problem i have is that the EM database get full too quick(its normal state is 15% free space or less) and each 3-5 months i have to increase it(i increase its capacity in 15% each time) because it get full, and i want to know if its normal. That problem doesn't happen with the CTM-Server database(it has 50% free space). The CTM-Server database has never been  below 50% free space. Below are the settings in CTM that could impact DB capacity:


      - The normal job load in the CTM is 430 jobs per day.

      - I keep the archived view point from the last 8 days(logs, sysout,etc...)

      - The initial value of the EM database was 3 GB. Now it has 8GB(6GB DATA and 2GB LOG)



      Any help will be good received.

        • 1. CTM-EM Database get full to quick.
          Graham Harper

          Hi Elvin.


          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.

          • 2. CTM-EM Database get full to quick.



            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?

            • 3. CTM-EM Database get full to quick.
              Graham Harper

              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.

              • 4. CTM-EM Database get full to quick.

                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