How to manually purge siteprotector database

WARNING: This solution requires that you edit your SQL or MSDE database. Incorrectly editing your database may cause severe and irreparable damage and may require you to reinstall your database. It is recommended that you backup your database before performing any edit to it. Editing of your database should only be performed by a SQL database Administrator, or similarly qualified individual. If you are not familiar with SQL or MSDE, do not attempt this operation! IBM Internet Security Systems cannot guarantee that problems resulting from incorrectly editing your database can be solved. Proceed at your own risk. Some commands only valid in SQL 2000 but not SQL 2005 and vice versa.
NOTE: You must be logged in with local administrator privileges on the MSDE or SQL machine or the following procedures will not work. Before performing any maintenance on your database, you must first stop the Event Collector to prevent damage to the database. Restart the Event Collector only after you have completed the maintenance.
These queries can be run from Microsoft Query Analyzer using the RealSecureDB, or by using osql. To use osql, type "osql -E -d RealSecureDB" at a command prompt, then type the query and the enter key, then type "go" and enter.
Increase the size of the database
If your database is 100% full, the size of the database may have to be increased before you can purge or clear your database. Keep in mind, you can not increase your database larger than the size of the hard drive the database is on, or past 2GB if you are using MSDE. If you have your database set to grow, and you still have plenty of space on your hard drive, but your database keeps getting filled at 100%, you may also want to increase the growth rate of your database.
Increase the size of your data file
Alter database RealSecureDB modify file (name='SiteProtector site database primary data file', maxsize= 2GB)
Note that the maxsize is the size to which it will be increased. In this example, the database is being increased to 2GB. Make this number whatever you feel is best for your environment.
To force increase the size of the data file, execute the statement below:
alter database RealSecureDB modify file (name='SiteProtector site database primary data file', size= 2GB)
Increase the size of your transaction logs
Alter database RealSecureDB modify file (name='SiteProtector site database log file', maxsize= 2GB)
Again, in this example the size has been increased to 2GB.
To force increase the size of the log file, execute the statement below:
alter database RealSecureDB modify file (name='SiteProtector site database log file', size= 2GB)
Purging the database
Purges are useful because data can be removed based on a date. Data that is older than the date supplied will be purged. However, purges may take a very long time to run (possibly hours), especially with a large database, In the process, all data is compared to the date you supplied.
Information about all the different purge stored procedures is located in Knowledgebase Article #1146.
The most common procedures that you will use are iss_PurgeObs and iss_PurgeSD:
iss_PurgeObs - Removes records from the Observances tables. These records correspond to the first level event information seen in the console.
iss_PurgeSD - Removes records from the SensorData tables. These records correspond to the detailed information seen when drilling into events on the console.
All of the purge stored procedures will be called with a query like these for examples:

exec iss_PurgeSD @BeginDate='March 26, 2006', @ObsType='0,1,2,3,4,5,6,7,8,9,10'
This would purge all type of data before 3/26/2006.
exec iss_PurgeSD @BeginDate='March 26, 2006', @ObsType='0,1'
This would purge incomplete and intrusion detection data before 3/26/2006.
Note: For SiteProtector 2.0 SP6.1 and Above use the command below:
exec iss_PurgeSD @BeginDate='DATE', @ObsType='0,1,2,3,4,5,6,7,8,9', @PurgeFlag=1
Users can purge only cleared events by adding @clearedonly=1. This will only purge the data that has been cleared in the SiteProtector Console's Analysis View. An example is shown below:
exec iss_PurgeObs @BeginDate='March 26, 2006', @ObsType='0,1,2,3,4,5,6,7,8,9,10', @PurgeFlag=1, @clearedonly=1

ObsType data should be purged from the database. The descriptions are provided below:
Type Description
-------------- ------------------------------
0 Incomplete data
1 Intrusion Detection
2 Vulnerability
3 Informational Only
4 AntiVirus
5 Firewall
6 WebFilter
7 AntiSpam
8 Application Compliance
9 Network Anomaly Detection
10 File Integrity
The status of a purge can be monitored while it is running by executing the following query:
exec iss_GetPurgeStatus

Clearing/Truncating the database
Truncating the database is much faster than purging the database because it simply deletes all of the event data from the database.
WARNING: Only do this if you want to remove ALL of your event data.
NOTE: This will not remove any license or component data.
The query to truncate the event data is:
exec iss_TruncateTables
The query to truncate the transaction logs is:
backup log realsecuredb with truncate_only


Backing up the database
To do a full backup the database to the location selected in the SiteProtector automatic maintenance, or "C:\" if the location hasn't been changed, run the following two queries:
exec iss_AddBackupDevice
exec iss_BackupDatabase 'RealSecureDB'
This may take a while depending on your database size.
Shrinking the database
To shrink the size of the database to allow for more disk space, run one of the following methods:
DBCC SHRINKDATABASE ('RealSecureDB')
This will shrink both the data and log files.
DBCC SHRINKFILE (name= 'siteprotector site database primary data file')
This will shrink the data file to the initial size if possible.
DBCC SHRINKFILE (name= 'siteprotector site database log file')
This will shrink the log file to the initial size if possible.