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 setup automatic table truncation on Single-User SQL Express systems.
Product Line
Andover Continuum
Environment
- Continuum Cyberstation
- Single-User
- SQL Express
- SQL Server 2005
Cause
Only the ExtendedLog table is automatically truncated on an Single-User SQL Express Continuum system. The other tables that can fill up quickly and need to be truncated regularly are not catered for (accessevent, activityevent, alarmevent, and errorevent).
Resolution
IMPORTANT: Make sure to perform a full SQL Database backup before attempting to make any changes to the database. (See Move / Backup / Restore a database on a Single User SQL2005 Express system for details on how to backup your database and, if not already installed, where to get Management Studio from)
Use the attached file SQLExpress to add table truncations.zip, to configure your Single-User SQL Express system to automatically truncate these tables when it does the ExtendedLog truncation. After downloading the file, follow the steps below.
- There are three files in the archive, these need to be copied to your Continuum folder on your Single-User system running SQLExpress as the database engine.
- LogTruncate.SQL
This file is the SQL script that performs the truncation of the four tables (accessevent, activityevent, alarmevent, and errorevent) to the number of days you specify - I have selected 90 days as my default but you can change this to whatever you require for each table. - CS_LogTruncate_OneOff.bat
This batch file will run the LogTruncate.sql script once when manually executed. You will need to change the server name "PC3" to whatever your PC's name actually is on your system. - CS_Hrly - with LogTruncat.bat
This is the batch file that will be renamed and replace the existing "CS_Hrly.bat" file currently located in your Continuum folder. This is the same as your current file except it has added the LogTruncate.sql script so that this will now be executed every hour along with the rest.
- LogTruncate.SQL
- Backup "CS_Hrly.bat" file in Continuum folder by renaming the file to something like "CS_Hrly ORIGINAL.bat".
- Rename "CS_Hrly - with LogTruncat.bat" to "CS_Hrly.bat", copy into (overtop the existing file if you haven't renamed it) the Continuum folder, and change the server name "PC3" to your PC's name. This should now execute this table truncation script along with the other database maintenance scripts every hour.
