Maintaining a Remote BarTender System Database

The BarTender System Database becomes larger as more information is logged. Although you can manage the contents of the BarTender System Database locally by using the Administration Console Maintenance page, you might sometimes need to perform regular maintenance from a remote source. Additionally, you might need to back up a remote database or re-index the contents of the database. For these regular tasks, including the removal of old database records, you can use the Maintenance Plan wizard in SQL Server Management Studio.

To use the Maintenance Plan wizard, you must have Microsoft SQL Server 2005 Service Pack 2 (SP2) or a later version of SQL Server installed. Additionally, verify that the Client Tools component is installed.

To locate the available database maintenance plans, open SQL Server Management Studio, and then connect to the server where the remote database resides. Under the SQL Server name in the Object Explorer, expand Management and then Maintenance Plans. You can schedule maintenance plans to run regular maintenance tasks on the BarTender System Database, such as performing database backups or indexing the records in the database.

If a maintenance plan that fits your needs does not exist, you can create a new one by using the SQL Server Maintenance Plan wizard, which you can access in the context menu of the Maintenance Plans folder. After you run the wizard, you can add additional tasks by modifying an existing maintenance plan.

The following example describes how to modify an existing maintenance plan so that it removes any records in the BarTender System Database that are older than five days old.

In the Object Browser of SQL Management Studio, locate the maintenance plan that you want in the Maintenance Plans subdirectory of the Management folder.
Open the context menu for the maintenance plan, and then click Modify. A design area is displayed that contains all of the tasks in the selected maintenance plan.
From the Toolbox, drag Execute T-SQL Statement Task onto the drawing surface.
Connect the arrow from an existing task to the new Execute T-SQL Statement Task.
Double-click Execute T-SQL Statement Task to edit the task.
In the text field, enter the SQL Script that you want. The following code removes any BarTender database records that are older than five days old:

DECLARE @timeThreshold int

DECLARE @timeUnits int

DECLARE @recordType nvarchar(1024)

DECLARE @recordType nvarchar(1024)
DECLARE @useLock int
DECLARE @lockHost nvarchar(1024)

DECLARE @result int

DECLARE @numRows int

--Choose how many units in the past

SET @timeThreshold = 5

--Where 0 = Days, 1 = Weeks, 2 = Months

SET @timeUnits = 0

SET @recordType = N'ALLR' --All Record Types

EXEC dbo.SpDeleteOlderRecords @timeThreshold, @timeUnits, @recordType

For more information about supported SQL scripts and stored procedures, refer to Stored SQL Procedures.