You have determined that you want to reduce the amount of data used by the IM Manager database.
The following tables are most commonly ones that use significant amounts of space in the IM Manager database:
|Table Name||Description||What Happens if Data Removed?|
|messages||Contains the contents and metadata of the IM conversations.||
IM conversations are no longer available.
Message detail reports are not available.
|file_transfers||Contains any file transfers performed in IM conversations (if IM Manager is configured to archive file transfers).||NOTE: This table automatically gets rows removed when data is removed from the messages table.|
|archived_messages||Keeps track of which messages have been archived by which export jobs.||NOTE: This table automatically gets rows removed when data is removed from the messages table.|
|impacttracking||Data on when IM Manager policies have been applied. For example, when IM Manager has blocked an IM login because the user was not registered.||Policy Action report will not have data.|
Determine the space used by each IM Manager table by running the following SQL command against the IM Manager database. You can use any SQL tool to perform this command.
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
The size of the IM Manager database is large.
The method to reduce the space used is to reduce the number of rows in the tables. This can be done by truncating tables or removing rows selectively (typically older data that is no longer necessary).
First determine if you wish to truncate any of the tables. Truncating the tables removes all rows in the table. None of that data goes to the database transaction log. If truncation is not used then removing older can reduce the size of the database. Follow the steps below for each table.
NOTE: When configuring an SMTP export job there is an option to "Purge automatically After Export". Using this option removes the exported data from the IM Manager database once it is written to a file on the file system. The email still must be sent by IIS SMTP to the archiving system. This process may involve multiple steps. Any one of those steps can fail. If the email is ultimately not archived by the archiving system the data is gone from IM Manager and cannot be retrieved. A database backup may be useful in retrieving the data.
Therefore it is not recommended to use the "Purge automatically After Export" option on the export job. Use one of the options listed below.
Using SQL Script to Purge Data
Some organizations want to purge data on a scheduled basis but not immediately with an export.
1. Download the attached SQL script.
2.Configure the script to remove messages older than a certain number of days.
By default the script removes messages older than seven hundred (700) days. If you wish to keep more than or fewer than seven hundred days:
a. Open the file purge.sql in an editor.
b. Change the value of the following line in the script to the number of days to retain messages
set @daysBack = 700
3. Configure the script for how to remove messages based on export status.
By default the script removes messages even if they are not exported. Those customers performing export of IM conversations may configure the script to only remove messages if they have been exported by any export job:
a. Open the file purge.sql in an editor.
b. Change the value of the following line to one (1) and the script will only remove messages if they have been exported:
set @export = 0
Schedule this SQL so it runs against the IM Manager database periodically.
Using OSQL to Purge Data from IM Manager MSDE Database
Standard SQL management tools may be used to purge data from the IM Manager database. Customer without standard SQL management tools may use OSQL to purge data from the IM Manager database.
1. Perform the steps listed above in the section Using SQL Script to Purge Data.
2. Download the attached batch file .
3. Save the purge.bat file in the same directory as the purge.sql script.
4. Run the purge.bat file.
5. At the prompt provide the password to the IM Manager database.
Using the Purge Once Feature
This type of purge cannot be scheduled. Follow these steps to purge data using the purge once feature.
1. Open the IM Manager Administration Console.
2. Click on the Administration tab.
3. Click on the Configuration|Export menu item.
4. Click on the purge hyperlink for the export job.
5. Fill in the date range to purge.
6. By default the purge job only removes data that was exported by the specific export job. To remove data even if it has not been exported by the export job click the Include Un-archived messages checkbox.
7. Click the Purge button.
Use the following SQL script to remove data older than a one hundred and eighty (180) days:
delete from impacttracking where actiontime < DATEADD(DD, -180, GETUTCDATE())
The number of days to retain can be changed in the SQL query.