This article applies to:

• Trustwave MailMarshal (SEG)
• Trustwave ECM/MailMarshal Exchange 7.X
• WebMarshal
• SQL Express

Question:

• How do I manually force a purge of a SQL database?
• What should I do when the SQL database has reached its maximum size limit (has grown too large)?

Symptoms:

• You may notice that new messages are not displayed in the MailMarshal Console.
• WebMarshal reports may not show recent data.

Causes:

The SQL Express database has reached the size limit as determined by Microsoft. (Note that these limitations are not determined by Trustwave or the Marshal Products.)

• SQL Express 2005 and 2008 limit each database to 4GB
• SQL Express 2008 R2, 2012, and 2016 limits each database to 10GB

Note: You can roughly determine the size of the database by checking the size of the .mdf file for the database. By default SQL databases are found in a "Data" subfolder of C:\Program Files\Microsoft SQL Server\

Resolution:

The simplest method to resolve database size issues is to reduce the logging retention period (and for MailMarshal, if necessary, the archive retention period). You can change these settings from the MailMarshal Configurator/Management Console or WebMarshal Console.

For MailMarshal, to purge records related to items in archive or quarantine folders, you MUST change the retention period for each folder.

Currently supported versions of MailMarshal and WebMarshal will efficiently purge database records that are no longer required. You should not need to purge data manually.

You may need to run a SQL "shrinkfiles" command to reduce the file sizes. See the notes at the end of this article.

If this issue continues to occur you should consider purchasing the appropriate licensed version of SQL Server. Licensed versions do not have a database size limitation.

Manual Purging:

Warning:  The SQL queries directly access your database and update or delete data. If a query is performed incorrectly, it can cause irreparable harm to the database and may result in loss of data. If you are unfamiliar with SQL or how to run a SQL query, please contact Trustwave Technical Support directly.

Warning:  Changing the records in any database table is NOT supported unless instructed to do so by Trustwave Technical Support.

MailMarshal SMTP, MailMarshal Exchange 7.X

Note: Purging does not remove records for archived or quarantined messages that are "younger" than the folder retention date. If you want to remove these records you MUST change the folder retention dates first. Purging does remove all message history records prior to the date you specify.

To purge a MailMarshal database from the command line in Microsoft SQL Server or MSDE, follow these steps:

1. Back up your database. If needed, refer to Q10221: "How do I back up my MailMarshal or WebMarshal database?"
   • You can determine the database name (as well as the server name and instance name, if required) in the Configurator, Server Properties, General tab
2. On the database server computer, open a command prompt.
3. Issue the osql statement below to purge information prior to the date specified:

   osql -U sa -S servername\instancename -d databasename -P password
1> exec dbo.PurgeMessages @PurgeDate = 'YYYYMMDD', @MaxRecords = 1000
2> go 
   

   Command Parameters
   
   

   servername	Specifies the name of the server.
   instancename	Specifies the SQL instance name. Note: The server and instance names allow you to connect to the correct SQL instance if you have more than one. For example: Myserver\SQLEXPRESS
   databasename	Specifies the database name.
   password	Specifies the sa account password.
   'YYYYMMDD'	Specifies the date you want to use as a threshold.  (Include the single quotes in the command syntax.) The procedure will delete records prior to this date. Note that this date format is guaranteed to be interpreted correctly regardless of locale settings.

    

4. In the command prompt window, you will receive confirmation of the number of items deleted.  Using the example command above, the number of items deleted will be at most 1000 due to the @MaxRecords limit setting. You can enter a larger number, or 0 to delete all the applicable records that predate the purge date.

   Warning: Setting the @MaxRecords limit to a high number (such as 1 million) or to 0, is NOT recommended where the system is live. The purge process creates delay or timeouts for other processes because it requires a lock on the data. To delete many records, run the process many times with a lower limit.

5. Repeat the exec dbo.PurgeMessages and go statements until you have recovered the desired space in the database, or until the process has no records remaining to purge.

WebMarshal 6.X or 7.X

To purge a WebMarshal database from the command line in Microsoft SQL Server, follow these steps:

1. Back up your database. If needed, refer to Q10221: "How do I back up my MailMarshal or WebMarshal database?"
   • You can determine the database name (as well as the server name and instance name, if required) in the Console, Server Properties, Reporting or Database tab
2. On the database server computer, open a command prompt.
3. Issue the osql statement below to purge information prior to the date specified:

   osql -U sa -S servername\instancename -d databasename -P password
1> exec dbo.PurgeLogData @PurgeDate = 'YYYYMMDD', @MaxRecords = 1000
2> go 
   

   Command Parameters
   
   

   servername	Specifies the name of the server.
   instancename	Specifies the SQL instance name. Note: The server and instance names allow you to connect to the correct SQL instance if you have more than one. For example: Myserver\SQLEXPRESS
   databasename	Specifies the database name.
   password	Specifies the sa account password.
   'YYYYMMDD'	Specifies the date you want to use as a threshold.  (Include the single quotes in the command syntax.) The procedure will delete records prior to this date. Note that this date format is guaranteed to be interpreted correctly regardless of locale settings.

    

4. In the command prompt window, you will receive confirmation of the number of items deleted.  Using the example command above, the number of items deleted will be at most 1000 due to the @MaxRecords limit setting. You can enter a larger number.
   
   Note: Setting the @MaxRecords limit to 0 will delete all the records that predate the purge date.   Be aware that the query could consume significant resources and run for a long time.
5. Repeat the exec dbo.PurgeMessages and go statements until you have recovered the desired space in the database, or until the process has no records remaining to purge.

Notes:

Purging records will not generally reduce the size of data files. You may need to run a SQL "shrinkfiles" command to reduce the file size.

If a user cannot log into the database because they have forgotten their password, they may be able to purge it by logging in as an administrator onto the machine where the SQL database is located. If the SQL database has been configured to accept mixed mode authentication they may have access to purge the database by using their windows credentials. 
The command is: osql -E -S servername\instancename -d databasename 

Related Knowledge Base articles: 

• Q10607: "Mail History and Quarantine Folders are not showing current information in the MailMarshal Console." This article includes steps for creating a batch file to automate the purging process.
• Q10221: "How do I back up my MailMarshal or WebMarshal database?"

This article was previously published as:

NETIQKB42588