Discussion :

[StringBuilder]

Bonjour,

Sur un serveur, j'ai plusieurs bases de données avec des politiques de retention des sauvegardes différentes.

Je ne trouve pas comment supprimer les anciennes valeurs d'historique de backup à partir d'une date pour une base donnée.

En effet :

sp_delete_database_backuphistory => Supprime TOUT l'historique pour une base donnée

sp_delete_backuphistory => Supprimer l'historique jusqu'à une date donnée pour TOUTES les bases

Y'a pas une procédure qui combine les deux ?

PS : Hors de question d'aller patouiller à la main dans les tables de MSDB.

[Invité]

Est-ce que ça pose un réel problème de mettre la limite à la date la plus ancienne ?

[StringBuilder]

En soit, non, c'est juste pas propre

Sinon, question subsidiaire : est-ce normal qu'une purge (bon, ok, pas mal de lignes... 2 ans d'historique de 30 bases à raison d'un backup toutes les 10 minutes) fasse complètement partir en vrille un serveur ?

Car là, depuis hier je tente de purger un de nos serveurs, et j'ai des trucs juste pas normaux...

On a des instances SQL Server 2014 et d'autres 2008 R2.

Tout est en version Express.

Sur les 2014, il y a moins de bases (donc forcément historique bien moins gros).
J'ai supprimé environ 1 année en 5 à 10 minutes sur chacun.

Pour les 2008 R2, plusieurs choses incompréhensibles...
1/ Si je supprime les données jusqu'au 01/01/2000, ça tourne pendant 2 heures (mais ça fini par aboutir).
=> Comment est-ce possible ? Il n'y a pas de backup antérieur à 2000, puisque les serveurs ont été installées (et les bases créées) courant 2014 ?
2/ Si je supprime ensuite les données jusqu'au 01/01/2014, c'est instantané (ou presque). Normal, y'a pas de backups non plus sur cette période.
3/ Si j'incrémente de 1 mois en 1 mois (donc pas des masses de données) au bout de quelques mois (dont la suppression dure quelques minutes), ça part en vrille total (2 heures sans réponse)

Bug ? Truc pas clair qui m'échappe ?

[StringBuilder]

Je pige plus rien...

Voici ma commande :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
declare @dte datetime;
set @dte = CAST('2014-01-01T00:00:00.000' as datetime);
exec sp_delete_backuphistory @dte;

Je lance, c'est immédiat.
J'incrémente la date de 1 jour par 1 jour.
2, 3, 4, 5, ... 14 => tout va bien.
15 : planté.
Je stoppe la commande.
Je fais COMMIT (car la procédure visiblement tourne dans une transaction et la laisse en vrac quand elle est arrêtée au milieu).
Je relance le 15 : instantané.
16, 17... rebelotte !

Et ainsi de suite. Je ne comprends pas.

[Invité]

Est-ce que les stats sont à jour sur msdb ?
Sinon, il y a certaines personnes qui ajoutent des indexes dans cette bd système.
Va voir ici : http://sqlperformance.com/2015/07/sql-maintenance/msdb
La source initiale semble être ici : weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx mais je n'arrive pas à charger la page.

[StringBuilder]

Je suis en train de faire un rebuild des index sur les deux serveurs...

Vu le temps que ça prend, effectivement, ça doit être un joyeux bordel...

-- Bon, ben après quelques minutes de rebuild... retour à la case départ : c'est toujours aussi lent

[SQLpro]

Pourquoi ne pas utiliser pour vos sauvegardes un unique fichier avec les options RETAINS_DAYS et EXPIRY DATE ?

A +

[Invité]

Quelles tailles font tes msdb ?

[StringBuilder]

J'ai pas tout compris

J'ai essayé au début d'indiquer un fichier unique en mode append, en indiquant une durée de validité pour les backups.
Mais d'après ce que j'ai constaté (et ce que j'ai lu) même si un backup est périmé, il reste dans le fichier de backup et n'est jamais écrasé.
=> Résultat, j'ai un fichier qui grossi de façon indéfinie, ce que je ne veux pas.

Moi j'ai un FULL toutes les 24 heures + un LOG toutes les 10 minutes. Et je conserve chaque fichier pendant 5 jours.
=> Passé ce délai, y'a quelques FULL qui sont conservés (suite à des mises à des faits marquants par exemple) mais globalement, aucun backup n'est conservé.

Le souci c'est que MSDB a grossi et dépasse les 4 Go... Un comble quand on sait que la plus grosse base de production fait moins de 400 Mo sur ces serveurs.
En même temps c'est logique : 1 backup toutes les 10 minutes pour 30 bases sur l'instance, ça donne 133 920 backups par mois...

Donc je voudrais purger l'historique des backups au dela de 5 jours : aucun besoin de les conserver, puisque de toute façon on n'a plus les sauvegardes.

[aieeeuuuuu]

Bonjour,

Une piste : n'y aurait-il pas par exemple un accroissement du TLog de la msdb lors de la suppression, à l'origine de ce ralentissement ?

Par ailleurs, quand on regarde la SP en question, on s'aperçoit qu'elle n'est pas franchement conçue pour traiter des milliers de lignes :

Utilisation de variable table dans un filtre INPar ailleurs, il n'y a pas d'index sur les colonne de date, ce qui doit pénaliser les requêtes...

[StringBuilder]

Je ne vois aucune activité disque sur ce fichier.
Principalement de la lecture de porc sur le DATA.

Je viens de créer un index sur la date. Rien ne change.

Je lance à la main le contenu de la procédure. J'ai remplacé les variables de type table par des tables temporaires avec une clé unique... rien de bien spectaculaire non plus.

[StringBuilder]

Après avoir réussi à réduire de plus de 60% l'utilisation des données dans la base MSDB, j'ai compris ce qui rend tout aussi lent...

Je ne sais pas de quelle taille c'est parti, mais visiblement de pas bien gros.

Et par défaut, accroissement de 10%...

Donc pour arriver à 6 Go, il en a fait des fragments...

En effet, là je fais un shrink avec reorganize pour réduire à 2 Go et... bah ça ramme pas qu'un peu ! Signe que c'est ultra-fragmenté

-- Edit : Grmpf. En fait, ça ramme tellement que ça plante en timeout

[StringBuilder]

Bon ben je pige pas comment c'est possible que ce soit aussi lent...

Sur le serveur de DEV c'est bon, mais sur la PROD, ça n'avance pas.

[Invité]

C'est quoi tes tables les plus pleines dans msdb ?
Est-ce que tu te sers de l'envoi d'emails ?

[StringBuilder]

Non non, y'a les 4 pauvres tables qui stockent l'historique des bacups qui sont pleines, et c'est tout... Plusieurs centaines de milliers de lignes.

J'ai fini par faire du DELETE de bourrin à la main sur les 4 tables.

Maintenant tout est rentré dans l'ordre.

Plus jamais de autogrowth sur MSDB, et plus jamais un serveur sans purge de cette table grmpf ! On m'y prendra plus