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.
