Discussion :

[Slaveak]

Bonjour à toutes et tous,

J'ai un problème récent, quand je lance de grosses requêtes en insert ou en delete, j'ai ce message : "Le journal des transactions de la base de données <ma_base> est plein en raison de 'ACTIVE_TRANSACTION' "

Or (et je vous explique le contexte):
- Mon mode de récupération est en simple. Il n'est donc pas sensé être plein, il est sensé se recycler non ?
- J'ai 6 Go pour ce journal de transaction.
- J'ai encore beaucoup de place sur le disque dur.
- C'est l’environnement de dev de mon entrepôt de données.
- Je suis assez débutant en gestion de base de données. A la base j'intègre des données via SSIS et ne m'occupe pas trop du versant administration du server SQL, mais je suis "obligé" (mais ça me plait hein !) de m'y coller.

Plus généralement j'ai un peu du mal avec le concept de journal de transaction. Je vois à peu près la différence entre FULL et SIMPLE et BULCK... Si je comprends bien un FULL et un BULCK se vide quand ils sont sauvegarder, et je n'ai pas besoin de m'occuper de la taille d'un SIMPLE...

Une idée d'ou pourrait venir le problème et une idée pour le régler ? N'hésitez pas si vous avez besoin d'autres renseignements (je compléterais de mon côté si je trouve d'autres infos)

Merci d'avance

Slaveak

[Invité]

- Mon mode de récupération est en simple. Il n'est donc pas sensé être plein, il est sensé se recycler non ?

Il se recycle mais pas tant que la transaction courante est terminée.
Et vraisemblablement, ta transaction requiert plus de 6 Go de JT.
Soit tu augmentes tes fichiers de JT, soit tu vois pour optimiser / fragmenter ton process.

[Slaveak]

Ah ok... c'est donc bien ce que je pensais alors... Il ne tient pas la transaction...

soit tu vois pour optimiser / fragmenter ton process.

Le problème c'est que ces requêtes sont placées en ELT dans mes packages SSIS... donc malheureusement... je ne vois pas trop comment optimiser et fragmenter.

En tout cas merci pour ta réponse rapide.

EDIT: je sais que c'est totalement à ne pas faire... mais je l'ai mis en croissance illimitée... et bien j'ai toujours le même plantage.

[mikedavem]

Et vraisemblablement, ta transaction requiert plus de 6 Go de JT.

Ou que la plus ancienne transaction non validée a fait que le journal n'a pas pu se vider avant.

Dans ton cas Slaveak tu peux commencer par utiliser la commande suivante sur la base de données concernée:

Code :

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

DBCC OPENTRAN

Ce qui te donnera le numéro de session correspondant à ta session encore ouverte (SPID).
A toi de voir ce que tu veux faire avec .. Tu peux par exemple la supprimer (qui nécessitera de toute façon un rollback) avec la commande

Code :

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

KILL <SPID>

Plus généralement j'ai un peu du mal avec le concept de journal de transaction. Je vois à peu près la différence entre FULL et SIMPLE et BULCK... Si je comprends bien un FULL et un BULCK se vide quand ils sont sauvegarder, et je n'ai pas besoin de m'occuper de la taille d'un SIMPLE...

Le modèle de récupération SIMPLE permet d'enregistrer au minimum certaines opérations et le journal est automatiquement tronqué après qu'un CHECKPOINT soit passé et qu'aucune transaction ouverte / ou enregistrement ne soit nécessaire pour de la réplication par exemple. Ceci dit il arrive que certaines fois, comme le dit 7gyY9w1ZY6ySRgPeaefZ, ta transaction va nécessité une certaine quantité d'espace disque dans le journal avant d'être validé. Dans ce cas la taille du journal peut tout à fait augmenter. En résumé il faut toujours se soucier et contrôler les grossissements de fichiers qui peuvent survenir dans tous les modes de récupération SQL Server.

++









A+

[Slaveak]

Code :

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

DBCC OPENTRAN

Code :

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

KILL <SPID>

Rein que pour ça, merci ! Je ne connaissais pas cette commande, que je viens d'utiliser, mais rien qui traine...

Tu peux par exemple la supprimer (qui nécessitera de toute façon un rollback) avec la commande

Tu veux dire qu'il faut que je fasse un ROLLBACK manuel une fois le KILL effectué ?

Le modèle de récupération SIMPLE permet d'enregistrer au minimum certaines opérations et le journal est automatiquement tronqué après qu'un CHECKPOINT soit passé et qu'aucune transaction ouverte / ou enregistrement ne soit nécessaire pour de la réplication par exemple. Ceci dit il arrive que certaines fois, comme le dit 7gyY9w1ZY6ySRgPeaefZ, ta transaction va nécessité une certaine quantité d'espace disque dans le journal avant d'être validé. Dans ce cas la taille du journal peut tout à fait augmenter. En résumé il faut toujours se soucier et contrôler les grossissements de fichiers qui peuvent survenir dans tous les modes de récupération SQL Server.

Différentes questions :
- Est-ce que je peux anticiper la taille que va prendre une transaction avant de la lancer ?
- Comment fait on pour contrôler les grossissement des fichiers ? contrôle des fichiers des journaux de transac ? ou autre ?

En tout cas merci pour ton aide.

[mikedavem]

Rein que pour ça, merci ! Je ne connaissais pas cette commande, que je viens d'utiliser, mais rien qui traine...

Tu t'es bien mis dans le contexte de la base de données concernée dans ton cas? C'est important.
A moins que tu n'as plus de transaction active depuis ... ou que tu utilises le mode SERIALIZABLE mais j'en doute ici.

Tu veux dire qu'il faut que je fasse un ROLLBACK manuel une fois le KILL effectué ?

Non et c'est un manque de précision de ma part. le Kill va automatiquement invalider la transaction correspondante, ce qui provoquera automatiquement un ROLLBACK en arrière plan.

Différentes questions :
- Est-ce que je peux anticiper la taille que va prendre une transaction avant de la lancer ?
- Comment fait on pour contrôler les grossissement des fichiers ? contrôle des fichiers des journaux de transac ? ou autre ?

En tout cas merci pour ton aide.

Pour anticiper la taille, cela reste un exercice assez difficile à ma connaissance
Pour le contrôle il y a plusieurs possibilités. Deja il faut configurer une taille initiale suffisamment grande qui permettra d'absorber ta transaction sans déclenchement des accroissements automatiques (autogrow). Les autogrow sont des opérations bloquantes pour les transactions qui doivent attendre que le fichier finisse de s'agrandir avant de continuer.

L'autogrowth est contrôlable via les options de fichiers dans SQL Server ou commande ALTER DATABASE si tu préfères les commandes. De manière générale ne pas mettre un autogrow trop petit, ce qui va générer de la fragmentation logique du journal et NTFS par la même occasion. Ne pas laisser non plus en % car l'agrandissement est moins prédictible dans ce cas (10% de 1GB n'est pas le même chose que 10% de 500GB par ex).

++

[olivtone]

tu peux aussi essayer le Traceflag T610 qui minimise les logs

TF610 can be used to get minimal logging in a non-empty B-Tree. The idea is that when you insert a large amount of data, you don't want to create a lot of transaction log. Initially the idea was to automatically do this in the engine, but we ran into a bunch of issues and thus we put it under a traceflag.

lien :

http://sqlblogcasts.com/blogs/simons...ou-use-it.aspx

pour activer un traceflag sur une instance :

DBCC TRACEON (610);

pour le desactiver :

DBCC TRACEON (610, -1)
GO

[Slaveak]

Tu t'es bien mis dans le contexte de la base de données concernée dans ton cas? C'est important.
A moins que tu n'as plus de transaction active depuis ... ou que tu utilises le mode SERIALIZABLE mais j'en doute ici.

Oui oui j'ai bien visé la bonne base et même vérifié une deuxième fois que je trouvais justement assez étonnant qu'il n"y en ai aucune d'active vu la taille des logs. Dond non, je pense que mon INSERT est trop gros pour le journal de transaction tel qu'il est actuellement.

Pour anticiper la taille, cela reste un exercice assez difficile à ma connaissance
Pour le contrôle il y a plusieurs possibilités. Deja il faut configurer une taille initiale suffisamment grande qui permettra d'absorber ta transaction sans déclenchement des accroissements automatiques (autogrow). Les autogrow sont des opérations bloquantes pour les transactions qui doivent attendre que le fichier finisse de s'agrandir avant de continuer.

L'autogrowth est contrôlable via les options de fichiers dans SQL Server ou commande ALTER DATABASE si tu préfères les commandes. De manière générale ne pas mettre un autogrow trop petit, ce qui va générer de la fragmentation logique du journal et NTFS par la même occasion. Ne pas laisser non plus en % car l'agrandissement est moins prédictible dans ce cas (10% de 1GB n'est pas le même chose que 10% de 500GB par ex).

++

Ahh ok ! Merci pour ces précisions ! Bon avec tout ça je vais tenter de trouver la bonne taille, sans forcement mettre l'autogrow ( je me méfie un peu des trucs qui se font dans mon dos... car j'ai tendance à les oublier dans un coin.