Watchdoc - Watchdocstats database maintenance plan

Introduction

In Watchdoc, the tool usage statistics are saved in an SQL or PostgreSQL database named Watchdocstats by default.

 

As the database evolves and data varies, query processing may slow down over time due to index fragmentation. To avoid this issue, we recommend that you regularly clean up your database by following the maintenance plan provided.

 

This SQL/PostgreSQL database has no default maintenance plan. It is therefore necessary to design a maintenance plan using a SQL/PostgreSQL query that can be executed by a script.

 

It is recommended to execute this script every week.

The following scripts are designed for medium-sized environments.When updating a domain with a large number of servers, please adapt the script to avoid system overload.

Procedure

1. On the server (on which Watchdoc is installed or on the MS SQL^® or PostgreSQL^® server), open a text editor (like MS Notepad^®) ;

2. enter the following script:

   Sqlcmd –e -i C:\BackupDATA\WD_SQL_maintenance.sql -o C:\BackupDATA\logs.txt

   or

   Sqlcmd –e -i C:\BackupDATA\WD_PostgreSQL_maintenance.sql -o C:\BackupDATA\logs.txt

   In the code :

   • the path of the -i argument specifies the location of the maintenance script;

   • the path of the -o argument specifies the location where the logs will be recorded.

3. Save the file in ‘.bat’ format.

4. Click on the .bat file to start maintenance.

Detail of the WD maintenance.sql script

The maintenance script performs:

1. a backup of the database ;

2. a purge of data older than 2 years;

3. a reorganization of the indexes;

4. possibly a defragmentation of the database.

    

   -- SQL Maintenance plan V2

   -- Update: 2026

   DECLARE @BdName nvarchar(100) = N'watchdocstats'

   DECLARE @BackupPath nvarchar(100) = N'c:\backupDB\'

   DECLARE @FullBackupPath nvarchar(150)

   DECLARE @BackupName nvarchar(150)

   DECLARE @TimeTable table( Step int, Fonction nvarchar(20), Datestart datetime, temps VARCHAR(8))

    

   -- 1. SAVE WITH COMPRESSION

   SET @BackupName = @BdName + '_Full_' + CONVERT(varchar(2), (DATEPART(wk,GETDATE())%4)) + '.bak'

   SET @FullBackupPath = @BackupPath + @BackupName

   INSERT INTO @TimeTable(Step, Fonction, Datestart) VALUES (1, 'Backup', GETDATE())

   BACKUP DATABASE @BdName TO DISK = @FullBackupPath

   WITH NOFORMAT, INIT, NAME = @BdName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

   UPDATE @TimeTable SET temps = CONVERT(VARCHAR(8), (GETDATE() - Datestart), 108) WHERE Step = 1

    

   -- 2. DELETION OF OLD DATA (BATCH)

   INSERT INTO @TimeTable(Step, Fonction, Datestart) VALUES (2, 'Purge', GETDATE())

   -- Keep the Top loop system (2000) because it prevents transaction logs from becoming saturated.

   DECLARE @Rows INT = 1

   WHILE @Rows > 0

   BEGIN

   DELETE TOP (5000) FROM watchdocstats.dbo.documents WHERE docdateoperation < DATEADD(year, -2, GETDATE())

   SET @Rows = @@ROWCOUNT

   END

   -- (Repeat for incidents and queue counters if necessary.)

   UPDATE @TimeTable SET time = CONVERT(VARCHAR(8), (GETDATE() - Datestart), 108) WHERE Step = 2

    

   -- 3. INTEGRITY CHECK (CRITICAL)

   INSERT INTO @TimeTable(Step, Fonction, Datestart) VALUES (3, 'Check Integrity', GETDATE())

   DBCC CHECKDB (@BdName) WITH NO_INFOMSGS

   UPDATE @TimeTable SET time = CONVERT(VARCHAR(8), (GETDATE() - Datestart), 108) WHERE Step = 3

    

   -- 4. INDEX OPTIMIZATION (REORGANIZE RATHER THAN REBUILD IF POSSIBLE)

   INSERT INTO @TimeTable(Step, Fonction, Datestart) VALUES (4, 'Index Optiz', GETDATE())

   -- Note: We use REBUILD here to keep things simple, but without the SHRINK afterwards!

   EXEC ('ALTER INDEX ALL ON ' + @BdName + '.dbo.documents REBUILD WITH (ONLINE = ON)') -- ONLINE if Enterprise edition

   EXEC ('ALTER INDEX ALL ON ' + @BdName + '.dbo.incidents REBUILD')

   EXEC ('ALTER INDEX ALL ON ' + @BdName + '.dbo.transactions REBUILD')

   UPDATE @TimeTable SET time = CONVERT(VARCHAR(8), (GETDATE() - Datestart), 108) WHERE Step = 4

    

   -- 5. STATISTICS UPDATE

   INSERT INTO @TimeTable(Step, Fonction, Datestart) VALUES (5, 'Update Stats', GETDATE())

   EXEC ('USE ' + @BdName + '; EXEC sp_updatestats')

   UPDATE @TimeTable SET temps = CONVERT(VARCHAR(8), (GETDATE() - Datestart), 108) WHERE Step = 5

   -- Logs Report

   SELECT * FROM @TimeTable

 

It may be necessary to adjust the @BdName and @BackupPath values entered by default in the script if necessary by replacing them with the name assigned to the database and the path to the backup file

Download the WD_SQL_MaintenancePlan.txt script

Detail of the WD PostgreSQL maintenance script

-- PostgreSQL maintenance plan

-- Target: watchdocstats

-- Update: 2026

 

-- 1. DATABASE ANALYSIS (Updating statistics for the optimizer)

-- Helps PostgreSQL choose the best execution plans

ANALYZE;

 

-- 2. PURGE OF OLD DATA (> 2 years)

-- In PostgreSQL, DELETE is effective, but a VACUUM must follow.

DELETE FROM documents WHERE docdateoperation < NOW() - INTERVAL '2 years';

DELETE FROM incidents WHERE incdate < NOW() - INTERVAL '2 years';

DELETE FROM queuescounters WHERE cntdate < NOW() - INTERVAL '2 years';

 

-- 3. VACUUM(Cleaning up space left vacant by purging)

-- VACUUM ANALYZE frees up internal space and updates statistics after purging.

VACUUM ANALYZE documents;

VACUUM ANALYZE incidents;

VACUUM ANALYZE queuescounters;

 

-- 4. REINDEX (If the database is heavily used for writing)

-- Optional: only do this once a week, as it blocks writing to the table.

-- On recent versions (12+), you can add CONCURRENTLY to avoid blocking.

REINDEX TABLE documents;

REINDEX TABLE incidents;

 

Download the WD_PostgreSQL_MaintenancePlan.txt script