Watchdoc - Maintenir la base de données SQL / PostgreSQL WatchdocStats

Introduction

Dans Watchdoc, les données statistiques sont enregistrées dans une base de données SQL^® ou PostgreSQL® nommée Watchdocstats par défaut.

Dans la mesure où les données de la base varient, il arrive qu'au fil du temps, le traitement des requêtes soit ralenti en raison d'une fragmentation des index. Pour éviter cet inconvénient, nous vous recommandons de nettoyer régulièrement votre base en suivant le plan de maintenance présenté.

Cette base de données ne dispose pas de plan de maintenance par défaut. Il est donc nécessaire de concevoir un plan de maintenance à l'aide d'une requête SQL / PostgreSQL et d'un script exécutable.

Il est recommandé d'exécuter ce script toutes les semaines.

Les scripts fournis sont conçus pour un environnement de taille moyenne. Dans le cas où vous mettez à jour la base dans un environnement plus important, veillez à adapter ces scripts afin d'éviter une saturation de l'espace disque ou une saturation des journaux de transactions qui pourraient entraîner un arrêt du serveur SQL/PostgreSQL.
Pensez, par exemple, à ajouter une pause dans la purge (WAITFOR DELAY), à gérer le log en ajoutant des BACKUP LOG dans les itérations de la boucle, à vérifier le taux de fragmentation des index et à utiliser un lecteur dédié où enregistrer les fichiers de sauvegarde.

Base de données MS SQL

Procédure de création du script

Pour exécuter le plan de maintenance par tâche planifiée, créez un script (.bat) dans un éditeur de texte :

sur le serveur MS SQL^®, ouvrez un éditeur de texte (de type MS Notepad) ;
saisissez le script suivant :

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

Dans ce code :
- le chemin de l'argument -i spécifie l'emplacement du script de maintenance ;
- le chemin de l'argument -o spécifie l'emplacement où sont enregistrés les fichiers traces (logs).
N.B.: pour plus d'information sur les commandes SQLCMD, consultez http://msdn.microsoft.com/en-us/library/ms162773.aspx

enregistrez le fichier au format ".bat" ;
cliquez sur le fichier.bat pour lancer la maintenance.

Détail du script WDmaintenance.sql

Le script de maintenance effectue :

une sauvegarde de la base de données : il peut être nécessaire d'ajuster les valeurs @BdName et @BackupPath inscrites par défaut dans le script en les remplaçant par le nom attribué à la base et le chemin d'accès au fichier de sauvegarde ;
une purge des données antérieures à 2 ans ;
une réorganisation des index ;
éventuellement d'une défragmentation de la base :

-- Plan de maintenance V2 (Optimisé)

-- Mise à jour : 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. SAUVEGARDE AVEC 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. SUPPRESSION DES DONNÉES ANCIENNES (BATCH)

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

-- Garde le système de boucle Top (2000) car il évite de saturer les logs de transaction

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

-- (Répéter pour incidents et queuescounters si nécessaire)

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

-- 3. VÉRIFICATION D'INTÉGRITÉ (CRITIQUE)

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

DBCC CHECKDB (@BdName) WITH NO_INFOMSGS

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

-- 4. OPTIMISATION DES INDEX (REORGANIZE PLUTÔT QUE REBUILD SI POSSIBLE)

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

-- Note : On utilise REBUILD ici pour rester simple, mais sans le SHRINK après !

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

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

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

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

-- 5. MISE À JOUR DES STATISTIQUES

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

Téléchargez le script pour une base SQL

Base de données PostgreSQL

Prérequis

Vérifiez les prérequis suivants avant de créer le script :

l'Autovacuum : assurez-vous qu'il est activé dans votre fichier postgresql.conf afin d'éviter que la base ne sature au quotidien ;
l'extension pg_repack : si le volume de données est important et qu'il n'est pas possible de bloquer les tables avec un REINDEX, pg_repack est l'outil standard pour reconstruire tables et index en ligne sans verrouillage ;
checkpoints : PostgreSQL gère l'intégrité via des "Checkpoints". Il n'y a pas d'équivalent exact au DBCC CHECKDB (de MS SQL) car PostgreSQL vérifie l'intégrité au moment de l'écriture/lecture (Checksums de pages).

Procédure de création du script

Pour exécuter le plan de maintenance par tâche planifiée, créez un script (.bat) dans un éditeur de texte :

sur le serveur Postgre SQL^®, ouvrez un éditeur de texte (de type MS Notepad) ;
saisissez le script suivant :

Sqlcmd –e -i C:\BackupDATA\WDmaintenance.pgsql -o C:\BackupDATA\logs.txt

Dans ce code :
- le chemin de l'argument -i spécifie l'emplacement du script de maintenance ;
- le chemin de l'argument -o spécifie l'emplacement où sont enregistrés les fichiers traces (logs).
N.B.: pour plus d'information sur les PostgreSQL, consultez https://docs.postgresql.fr/12/sql-commands.html

enregistrez le fichier au format ".bat" ;
cliquez sur le fichier.bat pour lancer la maintenance.

-- Plan de maintenance PostgreSQL V1

-- Cible : watchdocstats

-- 1. ANALYSE DE LA BASE (Mise à jour des statistiques pour l'optimiseur)

-- Aide PostgreSQL à choisir les meilleurs plans d'exécution

ANALYZE;

-- 2. PURGE DES DONNÉES ANCIENNES (> 2 ans)

-- En PostgreSQL, DELETE est efficace, mais un VACUUM doit suivre.

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 (Nettoyage des espaces vacants laissés par la purge)

-- Le VACUUM ANALYZE libère l'espace interne et met à jour les stats après purge.

VACUUM ANALYZE documents;

VACUUM ANALYZE incidents;

VACUUM ANALYZE queuescounters;

-- 4. REINDEX (Si la base est très sollicitée en écriture)

-- Optionnel : à ne faire qu'une fois par semaine car cela bloque l'écriture sur la table.

-- Sur les versions récentes (12+), on peut ajouter CONCURRENTLY pour ne pas bloquer.

REINDEX TABLE documents;

REINDEX TABLE incidents;

Téléchargez le script pour une base PostgreSQL.