HOW DO I MANUALLY FORCE A PURGE OF A SQL OR MSDE DATABASE?

Question:

• How do I manually force a purge of a SQL or MSDE database?
• What should I do when the SQL or MSDE 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 MSDE database has reached the 2GB size limit, or SQL Express has reached the 4GB size limit.

Note: You can roughly determine the size of the database by adding the sizes of the .mdf and .ldf files for the database. By default MSDE and SQL databases are found in a “Data” subfolder of C:Program FilesMicrosoft 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 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 M86 Technical Support directly.

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

MAILMARSHAL SMTP 6.X, 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

1. On the database server computer, open a command prompt.
2. Issue the osql statement below to purge information prior to the date specified:

osql -U sa -S servernameinstancename -d databasename -P password
1> exec dbo.PurgeMessages @PurgeDate = ‘YYYYMMDD’, @MaxRecords = 1000
2> go 

Command Parameters

1. 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 @MaxRecordslimit setting. You can enter a larger number.Note: Setting the @MaxRecords limit to 0 will delete all the applicable records that predate the purge date.  Be aware that the query could consume significant resources and run for a long time.
2. 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

To purge a WebMarshal 6.X 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 Console, Server Properties, Reporting or Database tab

1. On the database server computer, open a command prompt.
2. Issue the osql statement below to purge information prior to the date specified:

osql -U sa -S servernameinstancename -d databasename -P password
1> exec dbo.PurgeLogData @PurgeDate = ‘YYYYMMDD’, @MaxRecords = 1000
2> go 

Command Parameters

1. 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 @MaxRecordslimit 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.
2. 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 3.X

To purge a database for WebMarshal (versions 3.5 through 3.7.5) from the command line in Microsoft SQL Server or MSDE:

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, Logging tab

1. On the database server computer, open a command prompt.
2. Issue the osql statement below to purge information, retaining the number of days’ data specified:

osql -U sa -S servernameinstancename -d databasename -P password
1> exec dbo.PurgeLogData @NumDays = 'n'
2> go 

Command Parameters

1. In the command prompt window, you will receive confirmation of the number of items deleted.