Stand Alone Continuum using SQL Server 2005 Express is not purging the ExtendedLog table

[Product_Support]

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

After installing a new Stand Alone Continuum system or upgrading it to use SQL Server 2005 Express (SQL Express) it is found that the ExtendedLog table is not being purged. (truncate delete purge)

The tables fill up, resulting in a large Continuum database that can cause the system to slow down as it reaches its 4GB limit.

Product Line

Andover Continuum

Environment

• Continuum Stand Alone v1.82 and above.
• Continuum Stand Alone system that has been upgraded from v1.81 or below.
• SQL Server 2005 Express

Cause

Before Continuum v1.81, our Database engine for Continuum was MSDE. It used SQL Server Agent service to run maintenance tasks on the ContinuumDB. SQL Server 2005 Express does not have this SQL Server Agent and the scheduled maintenance tasks are added as Windows scheduled tasks during the creation of a new Continuum Database.

"SQL Agent is a SQL Server service that is used to automatically schedule job execution for SQL Server. It is typically used to schedule the execution of DTS packages or to perform system maintenance tasks like database backups. While MSDE includes the SQL Agent service, SQL Server 2005 Express does not. However, you can use the Windows built-in Task Scheduler to schedule jobs for SQL Server 2005 Express. You can use Task Scheduler in combination with the downloadable DTS runtime to automatically schedule the execution of DTS packages. Likewise, you can use Task Scheduler in combination with the SQL Server 2005 Express command-line SQLCMD tool to regularly execute SQL Server 2005 Express system maintenance jobs like database backups and other database access jobs."
See full article on this at http://technet.microsoft.com/en-us/library/cc966393.aspx

The key here is that the scheduled tasks are added during the creation of a new Continuum Database. When upgrading Continuum you would typically install overtop the existing installation; upgrading your existing Continuum Database, which does not create the Windows scheduled tasks required to schedule events tables and ExtendedLog table to be truncated on a regular basis.

Resolution

Check Purge interval

First thing to do is to confirm that your Extended Log Purge Interval has actually been set properly, by default this is set to 0 which disables automatic purging.

1. Right-click your Continuum icon and select General Preferences.
2. On line 10 - Extended Log Purge Interval, set this to the number of days you want to keep in your database. (typically 30 days)
3. Once this is set, the next time the scheduled task runs it will truncate your table back to the number of days you have selected.

If this was not set properly this may be all that is required. If it still does not purge and tasks have not been created then continue on with the below steps.

Creating Scheduled Truncation Tasks

Create a new Continuum database, at the end of this creation after closing the Database Initialization window, the scheduled tasks are created under Start -> Accessories -> System Tools -> Scheduled tasks.

To create these scheduled tasks and associated scripts you will need to delete the DB, create a new one, and then restore your backed up database overtop. To do this follow the below steps:

1. Shut down all Continuum Cyberstations.
2. 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)
3. Use SQL Management Studio Express and logon with the SA account.
4. Browse to the Databases folder, right-click the ContinuumDB and select Delete. This will delete the ContinuumDB from the server and the associated .Mdf and .Ldf files from the harddrive.
5. Open up Database Initialization.
6. Select Stand Alone.
7. Click on the Create New database radio button (bottom right) which opens up the needed fields.
8. Confirm the Database name is ContinuumDB.
9. Ensure the User Login ID is set to Andover97.
10. Ensure the User Password is set to (not disclosed due to public access).
11. Ensure the path is correct for the DB File location.
12. Enter in the SQL Server's SA password.
13. Enter the Windows username and password that has local administrator privileges. (IMPORTANT: Make sure that your Windows user has a valid username and password; the scheduled tasks will not be created if the Windows user does not have a password)
14. Click Continue (This creates the two files ContinuumDev.mdf and ContinuumLog.ldf in the DB File Location)
15. Click Close to close Database Initialization, this is the point at which the scheduled tasks and associated scripts are automatically created.
16. Using SQL Management Studio, now restore the backed up database.
    • Restore the backup over the top of the ContinuumDB on your new system:
      Right click on ContinuumDB -> Tasks -> Restore -> Database. Select "From device:" and add your Database file, making sure to tick the Restore checkbox to select the source. Next, go to the Options page and select "Overwrite the existing database" and press the "OK" button.
17. Change the db owner with the below query using the sp_changedbowner stored procedure. Select "New Query" and copy the query text shown below. Run the query.
    

    use ContinuumDB
    go
    sp_changedbowner 'Andover97'
    go

Now confirm that your Extended Log Purge Interval is set properly, by default this is set to 0 which disables automatic purging.

1. Right-click your Continuum icon and select General Preferences.
2. On line 10 - Extended Log Purge Interval, set this to the number of days you want to keep in your database. (typically 30 days)
3. Once this is set, the next time the scheduled task runs it will truncate your table back to the number of days you have selected.