Delete old logs in the Protect Server 3.8 or 3.10 database

This article applies to Workshare Protect Server 3.8 and 3.10.

Note: Removing log entries isn't the same thing as "shrinking" the size of the database. When log entries are removed, SQL assumes that the free space will be used again so it won't make the database file smaller. Instead, you'll notice that the database size stops growing for a while. After you remove the log entries, you can shrink the database.

1. Download and open the SQL script:

Download the attached SQL script.
Open SQL Server Management Studio.
Connect to your database hosting Protect Server as a user with dbadmin rights.
Click File.
Click Open.
Navigate to the .SQL script you downloaded.

2. Modify the script:
In SQL Server Management Studio, find the following lines at the top of the script. Modify as needed.

The name of your Protect Server database:

@database VARCHAR(255) = 'ProtectServerData',

Time and day that the logs will be removed (Format: HHMMSS – in this example, the script triggers at 11:10 PM):

@start_time INT = 231000,

The maximum number of days the logs are retained before they're deleted:

@days_to_keep_log_entries nvarchar(max) = '180',

The maximum number of log entries that can be removed per attempt. This script can consume 100% of a CPU core for a long amount of time (For example, 40,000 emails took about 40 minutes to delete):

@max_number_of_events_to_delete nvarchar(max) = '20000',

The user to run the job:

@job_user VARCHAR(255) = 'sa'

3. Install the scheduled job:

From SQL Server Management Studio, select Query Menu Item.
Click Execute.

4. Enable logging:
Ensure the script will generate logs/save logs to the right file:

In the Object Explorer, locate SQL Server Agent.
Click Jobs.
Click ProtectServer_DeleteLogs.
Click Properties.
Select Steps.
Select Step 1.
Click Edit.
Select Advanced.
Choose a file name for the Output file that will contain the logs.

5. Test the scheduled job:

Is the job created?
1. In Object Explorer, locate SQL Server Agent.
2. Click Jobs.
3. Click ProtectServer_DeleteLogs. You should be able to see the job.
Is it set to the right time?
1. In the Object Explorer, locate SQL Server Agent.
2. Click Jobs.
3. Click ProtectServer_DeleteLogs.
4. Click Properties.
5. Select Schedules.
6. Ensure it's set to the right time.
Did the last run succeed?
1. In the Object Explorer, locate Job Activity Monitor.
2. Click View Job Activity. this will show you how to find the results of the last run.

6. To trigger a job manually (optional):

In Object Explorer, locate SQL Server Agent.
Click Jobs.
Click Start Job at Step...

Delete old logs in the Protect Server 3.8 or 3.10 database

Learn how to make more space on your database by deleting old logs.