Warning
Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.
Issue
How to truncate event tables in Continuum databases.
Product Line
Andover Continuum
Environment
- Continuum 1.xx
- SQL 7
- SQL 2000
- SQL 2005
Cause
Stand Alone Continuum Databases use Microsoft MSDE and SQL Express databases which have size limitations. Once these databases reach full capacity they will need to have data truncated from tables to try and reduce the size of the database. Databases should never exceed their size limitations, but if they do, reducing the size of some of the tables will in most cases allow the database to be functional again.
Microsoft SQL MSDE database size limit is 2 gig
Microsoft SQL Express database size limit is 4 gig
Resolution
Once logged into the SQL MSDE or SQL Express database, the queries below will reduce the size of the database.
As with any database, make sure you backup the database before running any queries.
The Usernames and Passwords used below are defaults, your UN/PW may differ.
To log into SQL MSDE use the following commandline.
osql -Usa -P
To log into SQL 2005 Express us the following commandline with your servername
sqlcmd -Sput_server_name_here\sqlexpress -Usa -Padminadmin
Once logged in you'll be prompted with a 1> at which time you'll change the context of the database you'll be truncating information on. For example if your database name is ContinuumDB you would use the following.
1>Use ContinuumDB
2> Go
1>Now put in one of the queries below
2> type in Go to execute
/****** Replace 90 with the number of days to maintain in each log ********/ delete from accessevent where DATEDIFF(day, timestamp, getdate()) > 90 delete from activityevent where DATEDIFF(day, timestamp, getdate()) > 90 delete from alarmevent where DATEDIFF(day, timestamp, getdate()) > 90 delete from errorevent where DATEDIFF(day, timestamp, getdate()) > 90 ** To gain back the space after deleting rows SQL Express Right click on Continuumdb>Tasks>Shrink>Database MSDE(osql)
dbcc shrinkdatabase ('your_database_name',0) (enter)
dbcc shrinkdatabase ('your_database_name',0) (enter)
go (enter)
To truncate from the Full SQL server or SQL Express non command line interface, see Removing unwanted entries from an Alarm log, Access Event log, Error log, and Activity log automatic....
