Notes on database sizes

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 note is displayed on the start page of the NoSpamProxy Command Center under "Issues" 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 note is displayed on the start page of the NoSpamProxy Command Center under "Issues" and
• a notification is sent to the set administrator email address.

The reasons are listed below.

• 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.

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 the SQL Management Studio.
3. Log on to the SQL instance where the database is located. Usually these instances are 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 [NoSpamProxyIntranetRole]
   • Gateway Role: USE [NoSpamProxyGatewayRole]
   • Webportal: USE [NoSpamProxyWebPortal]
     USE [NoSpamProxyIntranetRole] / USE [NoSpamProxyIntranetRole] / USE [NoSpamProxyWebPortal] 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

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
  
• MessageTracking.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 too large a memory space for message tracking, which you can edit and reduce in the NoSpamProxy Command Center 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.