Notes on database size

Starting with NoSpamProxy version 13, NoSpamProxy informs about the fill level of the database if a Microsoft SQL Express version (max. 10GB per database) is filled by more than 70 percent.

In the following you will find some hints on how to react to a corresponding message in the console.

Warning Levels

NoSpamProxy warns you about a full database in two stages:

When the database is 70% full

• a message is added to the event log,
• a message is displayed under Incidents on the NoSpamProxy console start page, and
• a notification is sent to the set administrator email address.

When the database is 90% full

• a message is added to the event log,
• a warning is displayed on the NoSpamProxy console start page under Incidents, and
• a notification is sent to the set administrator email address.

What are possible reasons for a full database?

• The configured period of message tracking and its details (monitoring) is too long.
• There are problems with communication between two or more NoSpamProxy roles.
• Expired data has not been properly deleted from the database.

How to analyse the database

To find out why the database has reached the respective size, proceed as follows:

1. Install Microsoft SQL Management Studio on the system on which the affected database is installed.
   Microsoft SQL Management Studio is available free of charge from the Microsoft website.
2. Start SQL Management Studio.
3. Log on to the SQL instance where the database is located.
   These instances are usually called (local)\SQLEXPRESS or (local)\NOSPAMPROXY.
4. After successfully logging on, execute the following SQL queries (depending on the NoSpamProxy role involved); to do this, you only need to change the first row to the following databases:

Intranet Role:
USE [NoSpamProxyAddressSynchronization]

Gateway Role:
USE [NoSpamProxyDB]

Web Portal:
USE [enQsigPortal]

USE [NoSpamProxyAddressSynchronization]
GO
SELECT
isnull(t.NAME, 'Total') AS TableName,
s.name as SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS SizeInMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
ROLLUP(t.Name, s.Name, p.Rows)
HAVING p.rows is not null or (p.rows is null and t.name is null)
ORDER BY
sum(a.used_pages) desc
GO

How can the results be interpreted and solved?

In the output of the SQL script you can find an overview of all existing tables of the database as well as information about their size.

There are two specific tables that should be empty in normal operation or whose entries should change constantly each time they are called:

• DataReplication.Artefact
  

  Table Size of DataReplication.Artefact table

• MessageTracking.LegacyMessageTrackEntry
  

  Table Size of Table MessageTrack. LegacyMessageTrackEntry

If data accumulates in these tables but does not degrade, this indicates that problems exist. These must be clarified and solved by the NoSpamProxy support. In this case, please contact the partner responsible for you or – if you have purchased manufacturer support – the NoSpamProxy support directly.

All other scenarios indicate that the storage period for message tracking is too long, which you can edit and reduce in the NoSpamProxy console under Configuration > Advanced Settings > Monitoring. The reduction usually takes up to 24 hours, so that a result is usually not visible until the next day.