-- 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