Insufficient Disk Space on Local Database

Description

There is a similar error in the client's log:

Could not allocate a new page for database 'eWay' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Version

All versions of eWay-CRM

Difficulty

High

Resolution

SQL Server LocalDB and Express has a limit of 10 GB for a database.

Using the following query, you can find out what table or index takes the most space of your database and you should focus on it:

SELECT
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'IndexSize(KB)'
FROM sys.indexes AS i
    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID, i.index_id, i.name
ORDER BY SUM(a.used_pages) DESC

The article contains the following solutions:

Decrease the number of marketing responses

We recommend to disable creation of responses of this type: Email sent and Email delivered. Information about which campaign went to a given contact can also be found on the Marketing tab.

The following command let you know how many of these responses are in your eWay-CRM and whether the solution can help. The query needs to be executed against server database.

SELECT COUNT(ItemGUID) FROM EWD_Journal WHERE EmailCampaignResponseType = 1 OR EmailCampaignResponseType = 2

This script deletes old response from the server database:

DECLARE @JournalGUID AS UNIQUEIDENTIFIER
DECLARE Journals CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
    SELECT ItemGUID FROM EWD_Journal WHERE EmailCampaignResponseType = 1 OR EmailCampaignResponseType = 2
    OPEN Journals

    FETCH NEXT FROM Journals INTO @JournalGUID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC eWaySP_DeleteItem @ItemGUID = @JournalGUID, @FolderName = 'Journal'

        FETCH NEXT FROM Journals INTO @JournalGUID
    END
CLOSE Journals
DEALLOCATE Journals

Lower the size of notes in emails

If one of the biggest clustered indexes is IX_Emails, it is most probably related to the size of the Note field.

When eWay-CRM saves an email it also saves its text as a plaintext to the Note field. But this can be thousands of characters for each email.

Since eWay-CRM 8.0 we can trim notes using eWaySP_TrimEmailsNote procedure. The procedure has these parameters:

  • @MonthsBack - how old emails will be trimmed (the default is 12 months)
  • @MaxLength - what will be the size of the note that will be left (the default is 300 characters). This is enough to have a brief review of your emails, for example, in the HUB.

The whole content of emails will be available in the original message that you will still be able to open.

If you want to set automatic trimming, create a job like this one:

<TriggerDefinition When="ScheduledAtTime" Active="true">
  <Action Type="StoredProcedure">
    <StoredProcedures>
      <StoredProcedure Name="eWaySP_TrimEmailsNote"/>
    </StoredProcedures>
    <Criterias>
      <ActionCriteria Name="Time" Value="06:00:00"/>
      <ActionCriteria Name="Repeat" Value="w"/>
      <ActionCriteria Name="DayOfWeek" Value="Monday" />
    </Criterias>
  </Action>
</TriggerDefinition>

What to do when the local database contains changes and cannot be synchronized

First, apply some of the above-mentioned changes on your server.

Close Outlook on your PC.

First of all try to defragment indexes on the local database:

EXEC eWaySP_DefragmentIndexes

Open Outlook and wait until synchronization finishes. If there is still a synchronization issue, based on the applied procedure above, identify the table on your local database and find out whether there are some unsynchronized changes. The query for journals would look like this:

SELECT * FROM Journal WHERE ItemState <> 1

If the query returns nothing, you can delete the table:

TRUNCATE TABLE Journal

Run synchronization by folder using Windows registry to download missing items. Just download and open this file: https://download.eway-crm.com/regfiles/request_synchronize_all_counter.zip

Open Outlook and wait until the synchronization ends.