Maintaining a Remote BarTender System Database

The BarTender System Database becomes larger as more information is logged. While you can manage the contents of the BarTender System Database locally using Administration Console's Scheduled Maintenance page, you may, at times, need to perform regular maintenance from a remote source. Additionally, you may 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.

In order to use the Maintenance Plan Wizard, you must have SQL Server 2005 Service Pack 2 (SP2) or greater installed. Additionally, verify that the Client Tools component is installed.

To locate the available database maintenance plans, open SQL Server Management Studio, and connect to the server where the remote database resides. Under the SQL Server name in the Object Explorer, expand Management, then Maintenance Plans. Maintenance plans can be scheduled 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 using the SQL Server Maintenance Plan Wizard, accessible in the context menu of the Maintenance Plans folder. After running the wizard, you can add additional tasks by modifying an existing maintenance plan.

The following example demonstrates how to modify an existing maintenance plan, so that it removes any records in the BarTender System Database that are greater than 5 days old.

Locate the desired maintenance plan in the Object Browser of SQL Management Studio. It will be found in the Maintenance Plans subdirectory of the Management folder.
Open the context menu for the maintenance plan and select Modify. This will open a design area containing all of the tasks in the selected maintenance plan.
In the Toolbox, select Execute T-SQL Statement Task and drag it onto the drawing surface.
Connect the arrow from an existing task to the new Execute T-SQL Statement Task.
Double-click on the Execute T-SQL Statement Task to edit the task.
Enter the desired SQL Script into the text field. The following code removes any BarTender database records older than 5 days:

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 additional information on supported SQL scripts and stored procedures, refer to Stored SQL Procedures.