This article applies to:

• MailMarshal SMTP 6.X
• WebMarshal
• MSDE 2000
• SQL Express

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 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 or WebMarshal Console.

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:  Changing the records in any database table is NOT supported unless instructed to do so by M86 Technical Support.

MailMarshal 6.X

To purge a MailMarshal 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 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.) 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.

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

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
2. On the database server computer, open a command prompt.
3. Issue the osql statement below to purge information, retaining the number of days' data specified:

   osql -U sa -S servername\instancename -d databasename -P password
1> exec dbo.PurgeLogData @NumDays = 'n'
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
   database name	Specifies the database name.
   password	Specifies the sa account password.
   'n'	Specifies the number of days of data you wish to keep in your database.  (Include the single quotes in the command syntax.)

    

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

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.
• Microsoft Knowledge Base Article 322336: "How to Verify and Change the MSDE System Administrator Password"
• Microsoft Knowledge Base Article 272318: "How to shrink the database transaction log" This article includes steps to further shrink the database.
• Q10221: "How do I back up my MailMarshal or WebMarshal database?"

This article was previously published as:

NETIQKB42588