Clean up the drive space

It is important to delete outdated and irrelevant records in a timely manner when you maintain a large database. As a result, the database server will use less space on its drive, which improves the database performance

Possible reasons for quick database growth 

The following factors may lead to rapid database growth:

• Incorrectly set up or redundant record permissions. For example, the record permissions are set individually for a large number of users not arranged in groups. In this case, we recommend changing the settings and updating Creatio access permissions. Learn more in a separate guide: Access management.
• Lack of restrictions on file upload to Creatio. The files can be added by employees, uploaded as part of the email synchronization, or attached to self-service portal messages. We recommend limiting the size of uploaded files to 10 Mb. You can change this value in the “Attachment max size” (“MaxFileSize” code) system setting. We also recommend checking the uploaded files for relevancy and deleting the irrelevant files regularly. You can implement this feature using a business process.
• The synchronization of all emails from user mailboxes. We recommend selecting only the mailbox folders whose letters you must process in Creatio. For example, Important or Starred. Learn more in a separate article: Receive emails in Creatio.
• Processes traced for prolonged periods. Processes are usually debugged on a development environment or a test site. If you must collect the debugging information on a production site, we recommend turning the tracing off as soon as you finish analyzing the process execution problems. Learn more in a separate article: Trace process parameters.
• Incorrectly set up business process execution logic that causes the process to have the “Running” status for longer than needed. In this case, Creatio will keep the temporary files related to the process execution. We recommend modeling business processes so that they have definite completion conditions and do not have the “Running” status for longer than several hours. Learn more in a separate article: View process execution data.
• Incorrectly set up data reading in business processes. Creatio stores the values the business process retrieves as part of the Read data element execution in temporary data tables until the process finishes. If the business process does not require the values of all object columns, we recommend specifying the exact list of values to read. This will help you to reduce the amount of temporary data stored in Creatio greatly. Learn more in a separate article: Read data process element.
• Excessive change logging. We recommend turning on the change logging only for the sections in which you must track the data dynamics. For example, the product catalog. If you want to save the record change information, clean up the change log from the irrelevant data regularly. Learn more in a separate article: Clear the change log.
• Incorrectly set up integration of external services with Creatio. If the external services send a Creatio request that lacks the ForceUseSession header, they will need to re-authenticate. Learn more in developer documentation: EntityDataService.svc web service (OData 3).
• Connecting unnecessary cultures. This will cause Creatio to download translation files, which are then updated in each new Creatio version. We recommend connecting only those cultures that your company's employees use and removing any obsolete cultures.

Creatio database cleanup tools 

There are several ways to clean up the drive space in Creatio:

• Configure the process log archival and automatic cleanup.
• Clear the change log.
• Delete section records.
• Delete data as part of a business process.

Automatic process log cleanup 

Creatio can log the processes it runs. Logs help to track diagram bottlenecks and optimize them, as well as to analyze the efficiency of your employees. Creatio automatically archives the logs older than 30 days to reduce the amount of used space. The archived records remain available for 360 days before Creatio deletes them automatically. You can manage when to archive the records and for how long to store them.

Learn more in a separate article: Archive the process log records.

Clear the change log 

Clear the change log history to avoid storing irrelevant records in Creatio. We recommend clearing the change log regularly to ensure that the Change log section contains only the currently relevant information.

Learn more in a separate article: Clear the change log.

Delete section records 

Creatio may store irrelevant section records. You can delete such records from any Creatio section individually or in bulk. If the record you want to delete is connected to other sections, Creatio will ask you to review the connections and confirm what to delete. You can delete all of the information or only the selected record and keep the connected data.

Learn more in a separate article: Delete linked records.

Delete data as part of a business process 

Automate the drive cleanup with business processes. Use the Delete data process element to delete one or more records that meet specific conditions from any Creatio object. For example, create a business process that deletes all scheduled activities that were canceled. Set this process to run:

• On a timer, at a specific time. This is useful if you want to run the process periodically, e. g. once a month, and during the minimum load period, e. g. at night.
• After a specific event. This is useful if you want to run the process automatically and only when there is Creatio data to delete.
• Manually. This is useful if you want to run the process at any required moment.

Learn more in a separate article: Delete data process element.

Delete unused cultures 

You can delete cultures that you no longer plan to use to free up additional disk space. To delete a culture, use the following script:

IF OBJECT_ID('tempdb..#UsedCultures') IS NOT NULL
       DROP Table #UsedCultures
-- Get a list of used cultures
SELECT DISTINCT cult.Id
INTO #UsedCultures
FROM SysCulture cult
INNER JOIN SysAdminUnit au
       ON au.SysCultureId = cult.Id
INSERT INTO #UsedCultures
	(Id)
SELECT
	SysSettingsValue.GuidValue
FROM
	SysSettingsValue
INNER JOIN SysSettings
	ON SysSettings.Id = SysSettingsValue.SysSettingsId
WHERE
	SysSettings.Code = 'PrimaryCulture'

-- Get a list of tables from which to delete data
DECLARE TableNamesCursor CURSOR FOR
SELECT
        t3.TABLE_NAME AS ChildTableName  
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS t1 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t2 ON t1.UNIQUE_CONSTRAINT_NAME = t2.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t3 ON t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
WHERE
       t2.TABLE_NAME = 'SysCulture'
       and t2.COLUMN_NAME = 'Id'
       and t3.COLUMN_NAME = 'SysCultureId'
DECLARE @TableName SYSNAME
OPEN TableNamesCursor
FETCH NEXT FROM TableNamesCursor INTO @TableName
WHILE @@FETCH_STATUS = 0  
BEGIN  
       PRINT @TableName
       DECLARE @Sql NVARCHAR(MAX);
       SET @Sql = 'DELETE FROM ' + @TableName + '
            WHERE SysCultureId NOT IN (SELECT Id FROM #UsedCultures)';
       PRINT @Sql
       EXECUTE sp_executesql @Sql

       FETCH NEXT FROM TableNamesCursor INTO @TableName
END
CLOSE TableNamesCursor
DEALLOCATE TableNamesCursor
DELETE FROM SysCulture
WHERE Id NOT IN (SELECT Id FROM #UsedCultures)
IF OBJECT_ID('tempdb..#UsedCultures') IS NOT NULL
       DROP Table #UsedCultures

BEGIN;
-- Get a list of used cultures
CREATE TEMP TABLE "UsedCultures" ON COMMIT DROP AS
SELECT DISTINCT cult."Id"
FROM "SysCulture" cult
INNER JOIN "SysAdminUnit" au
ON au."SysCultureId" = cult."Id";
INSERT INTO "UsedCultures" ("Id")
SELECT "SysSettingsValue"."GuidValue"
FROM "SysSettingsValue"
INNER JOIN "SysSettings"
ON "SysSettings"."Id" = "SysSettingsValue"."SysSettingsId"
WHERE "SysSettings"."Code" = 'PrimaryCulture';	
-- Get a list of tables from which to delete data
DO $$
DECLARE
TableNamesCursor REFCURSOR;
TableName varchar;
BEGIN
OPEN TableNamesCursor FOR
SELECT kcu.TABLE_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    ON ccu.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG
    AND ccu.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
    AND ccu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON kcu.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
    AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
    AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE ccu.COLUMN_NAME = 'Id'
  AND ccu.TABLE_SCHEMA = 'public'
  AND ccu.TABLE_NAME = 'SysCulture';

--Delete localizations
LOOP
FETCH TableNamesCursor INTO TableName;
EXIT WHEN TableName IS NULL;
RAISE NOTICE 'Deleting from table %', TableName;
EXECUTE format('DELETE FROM %I WHERE "SysCultureId" NOT IN (SELECT "Id" FROM "UsedCultures")', TableName);
END LOOP;
CLOSE TableNamesCursor;
END $$;
DELETE FROM "SysCulture"
WHERE "Id" NOT IN (SELECT "Id" FROM "UsedCultures");
COMMIT