Knowledge Base
cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to truncate event tables in Continuum once ContinuumDB is at or near the maximum database size for MSDE or SQL Express.

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

The size of ContinuumDB has reached or is near the 2Gb limit imposed by MSDE.

The size of ContinuumDB has reached or is near the 4Gb limit imposed by SQL Express.

Environment

  • MSDE
  • SQL Express
  • Continuum Stand Alone.

Cause

The database is well over the SQL size limit (2Gb for MSDE, 4Gb for SQL Express)

Since the database size has already exceeded SQL's limitations it is not possible to use the deletion query statement to truncate the log tables.  This is because SQL will attempt to guard against data loss during the deletion process by making a copy of the tables contents before deletion occurs. Before a single row has been deleted from the table the size of the database will actually grow larger, exacerbating the problem.

Resolution

You must delete the contents of each log table entirely (Alarm, event, error, log) using the TRUNCATE statement. This will delete the contents of these tables entirely without first storing them the data.

Please note, you must...

  • Make arrangements with the customer so that they can archive any log and event data they wish to retain.
  • Backup the database before using the TRUNCATE TABLE command.

Here is the syntax for the TRUNCATE statements

TRUNCATE TABLE ContinuumDB.dbo.AlarmEvent
TRUNCATE TABLE ContinuumDB.dbo.AccessEvent
TRUNCATE TABLE ContinuumDB.dbo.ActivityEvent
TRUNCATE TABLE ContinuumDB.dbo.ErrorEvent
TRUNCATE TABLE ContinuumDB.dbo.ExtendedLog

Once the table have been truncated, Right click on ContinuumDB and select Shrink > Database.
Once the database has been size has been reduced, check the size and retest.

Please note that once the database size has exceeded SQL's limit it is likely that the database has been damaged.  This process may or may not correct damage that has occurred to the database.
 

Tags (1)
Labels (1)
Version history
Revision #:
1 of 1
Last update:
3 weeks ago
Updated by: