Discussion :

[StringBuilder]

Bonjour,

J'ai un serveur qui ne répond plus aux requêtes.
Il accepte les connexions, mais quand on lance une requête, il ne répond plus.

Je soupçonne (peut-être à tord) un fichier de dponnées plein (log ou data de tempdb certainement).

Voici les symptômes :
- Aucune requête lancée depuis les applications n'aboutit sur aucune base
- Je ne peux pas me connecter avec Management Studio
- Avec SQLCMD je peux me connecter, faire "use mabase" sans souci, mais aucune requête n'aboutit sur aucune base
- Encéphalogramme plat (ou presque) niveau disques
- CPU à 100% sur un coeur

Le process de sauvegarde qui tourne toutes les 10 minutes pour sauvegarer les logs indique qu'il n'arrive pas à mettre à jour MSDB

Que faire ?

Je n'ose pas redémarrer le service ni le serveur de peur que ça ne redémarre plus du tout.

Log du soft de backup :

Code sql :

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

1
2
3
4
5
6
7
 
Error during LOG backup of CRM on gshupdatebdd1.updatecld.local !!!
Le délai a été dépassé pendant l'attente de l'optimisation de la requête. Réexécutez la requête.
Impossible d'ajouter à la base de données msdb un enregistrement d'historique ou de détail de sauvegarde ou de restauration. Il y a sans doute un problème avec la base msdb. La restauration ou la sauvegarde a cependant réussi.
76 pages traitées pour la base de données 'CRM', fichier 'CRM_log' dans le fichier 1.
BACKUP LOG a traité avec succès 76 pages en 0.079*secondes (7.472*Mo/s).
Error during backup of CRM on gshupdatebdd1.updatecld.local. Will not clean up older backups

Sortie de SQLCMD :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
C:\Users\sdevidal>sqlcmd -E
1> use master
2> go
Le contexte de la base de données a changé*; il est maintenant 'master'.
1> use msdb
2> go
Le contexte de la base de données a changé*; il est maintenant 'msdb'.
1> select * from sys.columns;
2> go
 
Sqlcmd*: avertissement*: la dernière opération a été interrompue parce que l'uti
lisateur a appuyé sur Ctrl+C.
1> exec sp_spaceused;
2> go
 
Sqlcmd*: avertissement*: la dernière opération a été interrompue parce que l'uti
lisateur a appuyé sur Ctrl+C.
1> use master;
2> go
Le contexte de la base de données a changé*; il est maintenant 'master'.
1> select * from sys.database_files
2> go
 
Sqlcmd*: avertissement*: la dernière opération a été interrompue parce que l'uti
lisateur a appuyé sur Ctrl+C.
1>
2>
3>
4>
5> use crm;
6> go
Le contexte de la base de données a changé*; il est maintenant 'CRM'.
1> select count(*) from te_isi_fi;
2> go

[StringBuilder]

La moindre tentative d'accès à une donnée échoue.

En revanche, le moteur tourne bien :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Sqlcmd*: avertissement*: la dernière opération a été interrompue parce que l'uti
lisateur a appuyé sur Ctrl+C.
1> select 1 from te_isi_fi where id = 0
2>
3> go
 
Sqlcmd*: avertissement*: la dernière opération a été interrompue parce que l'uti
lisateur a appuyé sur Ctrl+C.
1> select 1
2> go
 
-----------
          1
 
(1 lignes affectées)
1>

[StringBuilder]

L'Agent SQL Server ne voulait pas redémarrer.

Au bout d'un moment le service a bien voulu redémarrer, et depuis tout est revenu dans l'ordre.

J'ai fait le tour des bases, et toutes ont tous leurs fichiers en autogrowth et aucun disque n'est plein...

Comprends pas...

[Invité]

tes traces parlent de problème sur msdb.
Est-ce que les fichiers de msdb sont en autogrowth ?
Est-ce qu'il n'y aurait pas une table énorme dedans ? J'avais déjà eu un problème avec une table système relative aux jobs qui grossissait en permanence mais c'était sur une version 2005 ou 2008.

[StringBuilder]

Merci pour ta réponse.

Oui, autogrowth est activé pour chaque fichier de chaque base de donnée (et ils sont tous à leur taille initiale, que j'avais largement sur-évalué histoire d'être tranquille).

Pour ce qui est de la table énorme dans MSDB j'ai effectivement le souci.
Je l'ai "résolu" en lançant, après chaque sauvegarde, une purge des traces de sauvegardes pour les fichiers qui n'existent plus (sinon SQL Server conserve la trace de TOUTES les sauvegardes de TOUTES les bases, y compris des logs)
=> Du coup un backup des LOG toutes les 10 minutes de chacune des bases, je te laisse imaginer le mammouth au bout d'un an... alors qu'on ne conserve ces backups que 3 semaines...

J'ai pas le code de mon programme sous la main, je le posterai demain.

[SQLpro]

L'agent SQL a une rétention paramétrable.

A +

[StringBuilder]

Paramétrage à quel endroit ?

Voici le code que j'ai dans mon programme :

Code sql :

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

1
2
3
4
5
6
 
use msdb;
go;
exec sp_delete_backuphistory @retention;
use master;
go;

[janlouk]

Paramétrage à quel endroit ?

Clic droit sur l'agent, properties/History

[elsuket]

Bonjour,

Il est aussi possible que les tables qui contiennent les historiques des jobs et des sauvegardes, si vous souhaitez tous les conserver sans période de rétention, ou une période de rétention assez longue, aient besoin d'index.
Typiquement, il est utile de créer les index suivants :

Code :

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

1
2
3
4
5
CREATE INDEX IX_backupset__media_set_id
ON msdb.dbo.backupset (media_set_id)
 
CREATE INDEX IX_backupset__backup_finish_date
ON msdb.dbo.backupset (backup_finish_date)

Par ailleurs sachez que les plans de maintenance et les emails envoyés avec la fonctionnalité Database Mail (sp_send_dbmail) voient leur caractéristiques écrites dans des tables de msdb.
En cliquant sur le nœud Tables de l'explorateur d'objets de SQL Server Management Studio (F8), puis en pressant F7, puis en double-cliquant sur l'icône de dossier System Tables, on obtient la liste des tables de cette base.
En cliquant-droit sur l'une des colonnes de ce rapport, on peut cocher Row Count et Data Space Used, ce qui devrait vous donner un indice
A défaut, vous pouvez écrire une requête avec sys.tables et sys.partitions

Que dit le journal d'erreur de SQL Server ? EXEC xp_readerrorlogEnfin, pour vous connecter à une instance SQL Server qui ne réagit plus, il suffit de s'y connecter avec une connexion d'administrateur dédiée :

• avec SQL Server Management Studio, en faisant précéder le nom de l'instance dans le dialogue de connexion par ADMIN:. Attention, aucune autre connexion ne doit être ouverte (pas d'explorateur d'objet).
• avec SQLCMD, de la même façon

Dans les deux cas, l'utilisateur qui réalise une telle connexion doit être membre du rôle de serveur sysadmin.

@++

[StringBuilder]

Au niveau du journal d'erreur, le souci c'est qu'il est flushé au redémarrage du service.

Les seules erreurs que j'ai, à intervalle régulier (tous les quarts d'heure), c'est ça :

2017-03-22 17:00:00.480 Logon Erreur*: 18456, Gravité*: 14, État*: 38.
2017-03-22 17:00:00.480 Logon Login failed for user 'UPDATECLD\servicesql'. Raison*: impossible d'ouvrir la base de données spécifiée explicitement 'PERF'. [CLIENT*: <local machine>]
2017-03-22 17:01:00.460 Logon Erreur*: 18456, Gravité*: 14, État*: 38.
2017-03-22 17:01:00.460 Logon Login failed for user 'UPDATECLD\servicesql'. Raison*: impossible d'ouvrir la base de données spécifiée explicitement 'PERF'. [CLIENT*: <local machine>]

Je sais pas d'où ça vient. Je sais pas ce qu'est cette base de données "PERF". En tout cas, le compte de service n'arrive pas à s'y connecter pour cause : la base n'existe pas !

Sinon, j'ai créé les index en question : chose étrange, je ne les vois pas dans l'explorateur d'objet ! (j'ai pas la ligne "index" sous la table).

[olivtone]

Question peut etre bete: mais pourquoi faire un backup log sur la msdb ? en mode simple ca fait largement l'affaire non ?

[StringBuilder]

[Edit] En fait, MSDB est backupé uniquement en FULL

Voici le code au début du backup LOG :

Code sql :

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

1
2
3
4
5
 
            if (this.Name == "master" || this.Name == "model" || this.Name == "msdb")
            {
                return;
            }

La seule base système dont je sauvegarde les logs est tempdb.

Ceci dit, ça ne m'explique pas pourquoi mon instance a planté et est repartie comme si de rien n'était après un redémarrage du service

[elsuket]

Au niveau du journal d'erreur, le souci c'est qu'il est flushé au redémarrage du service.

Pas exactement : le moteur les cycle, et il en garde 6 par défaut, mais vous pouvez le paramétrer : Maintenance > clic-droit sur SQL Server Logs > Configure
EXEC xp_readerrorlog, les lit tous (je crois). Sinon exécuter :

Code :

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

1
2
3
4
5
6
EXEC xp_readerrorlog 0, 1 -- Lit le journal d'erreur SQL Server courant
EXEC xp_readerrorlog 1, 1 -- Lit le premier journal d'erreur SQL Server précédant le journal courant
EXEC xp_readerrorlog 2, 1 -- Lit le deuxième journal d'erreur SQL Server précédant le journal courant
EXEC xp_readerrorlog 3, 1 -- Lit le troisième journal d'erreur SQL Server précédant le journal courant
EXEC xp_readerrorlog 4, 1 -- Lit le quatrième journal d'erreur SQL Server précédant le journal courant
EXEC xp_readerrorlog 5, 1 -- Lit le cinquième journal d'erreur SQL Server précédant le journal courant

Pour lire le journal de l'Agent SQL Server, remplacez le 1 final dans les appels ci-dessus par un 2. Paramètres :

- Le 1er est l'indice du log
- Le 2e est le type : 1 pour SQL Server, 2 pour SQL Server Agent
- Le 3e est une chaîne de type nvarchar (mettre N'maChaine' pour chercher le terme maChaine. La recherche se fait comme un LIKE avec un joker devant et derrière maChaine)
- Le 4e est aussi une chaine de type nvarchar
- Le 5e est une date de début de période, au type datetime
- Le 6e est la date de fin de période, aussi au type datetime
- Le 7e est l'ordre de tri. Par défaut c'est ASC, mais vous pouvez mettre DESC.

Je sais pas d'où ça vient. Je sais pas ce qu'est cette base de données "PERF". En tout cas, le compte de service n'arrive pas à s'y connecter pour cause : la base n'existe pas !

Déjà on sait que c'est une application qui s'exécute, à priori, sur le serveur : [CLIENT*: <local machine>].
Avec SQL Profiler, on peut démarrer une trace avec l'événement Exception. De là, on obtient les détails de l'erreur.
Attention à bien sélectionner le ClientProcessID : c'est le PID de l'application sur le serveur. On le retrouve dans le gestionnaire de tâches.
On peut aussi prendre ApplicationName. Vous pouvez aussi inspecter les tâches planifiées.
Au pire, vous pouvez créer une base PERF, et voir ce que cela donne : peut-être obtiendrez-vous des indices supplémentaires.

MSDB est backupé uniquement en FULL

Vous parlez de la sauvegarde ou du mode de récupération ?
Ce que je comprends du morceau de code, c'est que ces bases ne sont pas sauvegardées.
C'est dangereux, car vous pourriez en avoir besoin pour récupérer une instance crashée, surtout dans le cas de master et de model.
En ce qui concerne msdb, vous perdriez l'historique des sauvegardes, de l'exécution des plans de maintenance, des emails envoyés avec sp_send_dbmail, mais surtout les caractéristiques des jobs.
Au pire, vous pouvez restaurer un backup de msdb sur une autre instance, et récupérer leurs caractéristiques à partir de cette restauration.

@++

[elsuket]

La seule base système dont je sauvegarde les logs est tempdb.

C'est impossible. C'est la seule base dont on ne peut pas prendre de backup.
Cela ne présente en effet aucun intérêt puisqu'elle est reconstruite à chaque démarrage d'instance.
Dans l'état actuel des choses, c'est généralement tous les mois pour l'application des derniers patchs Microsoft

@++

[Invité]

2017-03-22 17:00:00.480 Logon Erreur*: 18456, Gravité*: 14, État*: 38.
2017-03-22 17:00:00.480 Logon Login failed for user 'UPDATECLD\servicesql'. Raison*: impossible d'ouvrir la base de données spécifiée explicitement 'PERF'. [CLIENT*: <local machine>]

Quelques pistes :
Est-ce que PERF est la bd par défaut du user UPDATECLD\servicesql ? Est-ce que cette bd existe encore ?
Est-ce que PERF est en auto-close ?
Si VM, est-ce que ça se passe pendant les backups VM ?

[StringBuilder]

Merci pour vos réponses.

Alors dans le désordre :
- TEMPDB : en effet, j'avais pas fait gaffe, TEMPDB, ainsi que les bases de Reporting Services (qui ne sont pas utilisées) ne sont pas backupées du tout.
- MSDB, MASTER et MODEL sont backupées en FULL toutes les 10 minutes, et sont en recovery simple (de toute façon, si on veut les passer en recovery full ça marche pas)
- Les autres bases (donc les bases "utilisateur" sont toutes forcées à recovery full et les logs sont sauvegardés toutes les 10 minutes, et les data toutes les 24 heures en full)

Avec une telle cadence pour les logs, même si j'ai une interface folle qui se met à modifier la moitié des données, vu que le programme fonctionne "ligne à ligne" et sans transaction, ça permet de garantir qu'ils restent à une taille raisonnable.

Pour ce qui est de xp_readerrorlog, je l'avais en effet fait tourner sans paramètre.

J'ai des centaines de lignes d'erreur (uniquement celle-ci).

Code :

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

1
2
3
4
5
6
7
 
2017-03-22 15:00:49.640	spid114	Erreur*: 8628, Gravité*: 17, État*: 0.
2017-03-22 15:00:49.640	spid114	A time out occurred while waiting to optimize the query. Rerun the query.
2017-03-22 15:00:55.090	spid112	Erreur*: 8628, Gravité*: 17, État*: 0.
2017-03-22 15:00:55.090	spid112	A time out occurred while waiting to optimize the query. Rerun the query.
2017-03-22 15:01:00.370	spid154	Erreur*: 8628, Gravité*: 17, État*: 0.
2017-03-22 15:01:00.370	spid154	A time out occurred while waiting to optimize the query. Rerun the query.

Au milieu j'ai des messages de backup terminés avec succès (comme quoi tout n'était pas croûté).

En fait, on dirait que c'était l'optimiseur qui était dans les choux... C'est possible ça ?


Edit : Pour ce qui est de la base "PERF", je pense que ça doit venir de l'outil de virtualisation en effet. Car (sûr et certain) j'ai aucun programme qui tente d'utiliser cette base (d'un point de vue logiciel c'est moi qui gère les softs qui y accèdent) mais je n'ai pas la main (ni la visu) sur la partie système. Ça pourrait venir du superviseur.
Quant à SERVICESQL c'est le compte utilisateur que j'ai fait tourner, et c'est avec cette identité que SQL Server tourne lui-même (c'est son compte de service). Donc "normalement", aucun programme ne l'utilise.

Je viens de créer une base vide. On verra bien si des trucs apparaissent dedans (ou si d'autres messages apparaissent).

[elsuket]

Désolé pour la réponse tardive :

[QUOTE - TEMPDB : en effet, j'avais pas fait gaffe, TEMPDB, ainsi que les bases de Reporting Services (qui ne sont pas utilisées) ne sont pas backupées du tout.[/QUOTE]
TempDB n'est effectivement pas sauvegardable, mais la base ReportServer devrait être sauvegardée : elle contient, entre autres, la définition des rapports et leur historique et statistiques d'exécution.

- MSDB, MASTER et MODEL sont backupées en FULL toutes les 10 minutes, et sont en recovery simple (de toute façon, si on veut les passer en recovery full ça marche pas)

Là on est un peu en mode ceinture + bretelles + casque + combinaison . Les bases systèmes peuvent être sauvegardées complètement une seule fois par jour.
En revanche, on peut passer le mode de récupération de la base de données model à FULL : ainsi, lorsqu'on crée une nouvelle base de données sur une telle instance, son mode de récupération est automatiquement FULL.
Attention : lors d'une restauration de base, c'est de mode de récupération de la base au moment de la sauvegarde qui est conservé.

- Les autres bases (donc les bases "utilisateur" sont toutes forcées à recovery full et les logs sont sauvegardés toutes les 10 minutes, et les data toutes les 24 heures en full)

Là c'est bon. Si le volume des sauvegardes devient trop élevé, envisagez des sauvegardes différentielles.

En fait, on dirait que c'était l'optimiseur qui était dans les choux... C'est possible ça ?

Oui, en général par manque de mémoire ou de ressources CPU.
Que disent les compteurs de performance Page Life Expectancy, Database Pages, et d'utilisation CPU ?
Il faut aussi regarder la quantité de mémoire utilisée par les divers composants internes de SQL Server. La requête suivante vous le donne :

Code :

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

1
2
3
4
5
6
SELECT	type
	, name
	, SUM(pages_kb) / 1024.0 AS clerk_size_MB
FROM	sys.dm_os_memory_clerks
GROUP	BY type, name
ORDER BY clerk_size_MB DESC

Normalement les deux premières lignes du résultat de cette requête devrait avoir leur colonne type à la valeur MEMORYCLERK_SQLBUFFERPOOL, CACHESTORE_SQLCP ou CACHESTORE_OBJCP.
Il arrive que ce soit le gestionnaire de verrous (OBJECTSTORE_LOCK_MANAGER). Vous pouvez le confirmer avec les compteurs de performance dont la catégorie porte le même nom.

@++