Datafly.pro

Quels sont les historiques à gérer ?

• L'historique des sauvegardes : stocké dans msdb
• L'historique des travaux : stocké dans msdb
• L'historique de database mail : stocké dans msdb et presque toujours oublié
• Les journaux de plans de maintenance et autres fichiers texte de rapports de travaux : fichiers dans le répertoire log (par défaut)
• On peut aussi penser à restreindre la période d'historisation du catalogue SSIS, qui a tendance à gonfler et à recycler l'errorlog de SQL Server s'il devient trop volumineux.

Dans les plans de maintenance de Management Studio, des tâches de nettoyage existent (historique des backups, travaux, plans de maintenance, rapports texte) mais je constate qu'elles sont trop souvent négligées.

Et si vous utilisez les scripts de maintenance de Ola Hallengren, que personnellement je préfère et recommande, ces tâches sont aussi prises en charge et sont juste à planifier :
purge de l'historique de backup et de travaux, purge de la table d'historique propre à ces tâches.

En tout cas, voilà quelques requêtes à mettre en place pour purger les historiques de msdb (avec une rétention de 30 jours dans cet exemple) :

USE msdb
GO
-- Backup history cleanup
DECLARE @DeleteBefore datetime
SET @DeleteBefore = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_delete_backuphistory @oldest_date = @DeleteBefore
GO
-- Job history cleanup
DECLARE @DeleteBefore datetime
SET @DeleteBefore = DATEADD(dd,-30,GETDATE())
EXECUTE dbo.sp_purge_jobhistory @oldest_date = @DeleteBefore
GO
-- database mail cleanup
DECLARE @DeleteBefore DateTime
SET @DeleteBefore = (Select DATEADD(d,-30, GETDATE()))
EXEC dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteBefore
EXEC dbo.sysmail_delete_log_sp @logged_before = @DeleteBefore
GO
-- maintenance plans cleanup
DECLARE @DeleteBefore DateTime
SET @DeleteBefore = (Select DATEADD(d,-30, GETDATE()))
EXEC dbo.sp_maintplan_delete_log null,null,@DeleteBefore

En ce qui concerne le nettoyage des divers rapports au format texte, la tâche de nettoyage du plan de maintenance est souvent trop limitée, se bornant à ne supprimer que les rapports produits par ces plans de maintenance. Je vous propose deux solutions :

• Une tâche en powershell

$d=Get-Date
Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG\*" -include *.txt | Where {($_.LastWriteTime -lt $d.AddDays(-30))} | Remove-Item

• Une tâche SQL, mais attention : cet exemple utilise une vue système et une procédure système étendue, toutes deux non documentées et disponibles seulement depuis SQL Server 2017 (avec quelques Cumulative Updates) ou SQL Server 2019. L'utilisation de fonctionnalités non documentées est à vos risques et périls !
  • DMV : sys.dm_os_enumerate_filesystem, nouvelle DMV vraiment très pratique, depuis SQL Server 2017 et ultérieur.
  • Procédure stockée étendue : sys.xp_delete_files, à ne pas confondre avec xp_delete_file (sans le "s") utilisée par les plans de maintenance, disponible depuis  SQL Server 2017 CU#18 et ultérieur.
  • Dans cet exemple, on supprime tous les fichiers TXT de plus de 30 jours dans le répertoire log par défaut de SQL Server.

USE master
GO
DECLARE @LogPath NVARCHAR(MAX)
DECLARE @Extension NVARCHAR(4) = 'txt'
DECLARE @DeleteBefore DATETIME
DECLARE @FileToDelete NVARCHAR(4000)
SELECT @LogPath=path FROM sys.dm_os_server_diagnostics_log_configurations;
SET @DeleteBefore = DATEADD(dd,-30,GETDATE())
DECLARE DeleteFileCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT full_filesystem_path
FROM   sys.dm_os_enumerate_filesystem(@LogPath,N'*.'+@Extension)
WHERE last_write_time < @DeleteBefore AND is_directory = 0
OPEN DeleteFileCursor
FETCH NEXT FROM  DeleteFileCursor INTO @FileToDelete
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Deleting file: '+@FileToDelete
    EXEC sys.xp_delete_files @FileToDelete
    FETCH NEXT FROM  DeleteFileCursor INTO @FileToDelete
END
CLOSE DeleteFileCursor
DEALLOCATE DeleteFileCursor