Le journal des transactions de la base de données est plein en raison de 'ACTIVE_TRANSACTION'

Version imprimable

01/06/2016, 14h20
Slaveak

Le journal des transactions de la base de données est plein en raison de 'ACTIVE_TRANSACTION'

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
01/06/2016, 14h59
Invité

Citation:

Envoyé par Slaveak

- 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.
01/06/2016, 15h15
Slaveak

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

Citation:

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

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.
01/06/2016, 15h19
mikedavem

Citation:

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:

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:

KILL <SPID>

Citation:

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+
01/06/2016, 15h27
Slaveak

Citation:

Envoyé par mikedavem

Code:

DBCC OPENTRAN

Code:

KILL <SPID>

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

Citation:

Envoyé par mikedavem

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

Citation:

Envoyé par mikedavem

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.
01/06/2016, 15h51
mikedavem

Citation:

Envoyé par Slaveak

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.

Citation:

Envoyé par Slaveak

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.

Citation:

Envoyé par Slaveak

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

++
01/06/2016, 16h07
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
01/06/2016, 16h08
Slaveak

Citation:

Envoyé par mikedavem

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.

Citation:

Envoyé par mikedavem

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.
01/06/2016, 16h13
mikedavem

Citation:

Envoyé par Slaveak

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.

Exactement, les autogrow doivent plus faire partie de l'exception que du commun. Dans un monde idéal, les autogrow se surveillent et des actions doivent être prises dans ce cas ...

++
01/06/2016, 16h26
Slaveak

Citation:

Envoyé par olivtone

pour activer un traceflag sur une instance :

DBCC TRACEON (610);

pour le desactiver :

DBCC TRACEON (610, -1)
GO

Merci pour le truc, mais je ne capte pas trop comment ça marche... ça annule le passage par le journal de transaction ? et on le réactive après ?
03/06/2016, 21h35
elsuket

Bonjour,

Avant de jouer avec le moteur et le drapeau de trace 610 (voir notamment sa documentation originale pour bien comprendre à quelle problématique il répond), il faut identifier la raison de la transaction restée ouverte, si toutefois il en existe bien une.

Vous pouvez utiliser une session d'événements étendus pour identifier quelle requête provoque un grossissement du fichier du journal des transactions.

Citation:

Merci pour le truc, mais je ne capte pas trop comment ça marche... ça annule le passage par le journal de transaction ? et on le réactive après ?

Tout changement provoqué par une transaction est écrit dans le fichier du journal des transactions (cf. ACID ;)).
Mais il y a des opérations, comme effectivement la maintenance des pages d'un index, pour lesquelles on peut journaliser moins de données que pour les opérations DML classiques dans une charge de travail de type OLTP.

@++ ;)
06/06/2016, 15h25
Slaveak

Citation:

Envoyé par elsuket

Vous pouvez utiliser une session d'événements étendus pour identifier quelle requête provoque un grossissement du fichier du journal des transactions.

Ok mais n'étant pas du sérail des DBA, une "session d’événements étendus" là ça ne me parle pas. Je vais me renseigner. Mais je pense que le problème vient basiquement d'ordres DML trop importants que le journal de transaction n'est pas capable d'enregistrer en entier.

Est-il possible de faire passer un ordre DML en lots successifs ? en gros, tu INSERT les 10 000 premières lignes, tu COMMIT, tu passe au 10 000 suivantes ?

PS:@ mikedavem & elsuket
Votre bouquin (SQL Server 2014) est devenu mon livre de chevet il est vraiment très complet (je suis d'ailleurs loin de l'avoir fini) et très bien fait ! Je vais d'ailleurs voir ou est traité ces sessions d’événements étendus dedans :p
EDIT : Ah ben oui ! Partie V, Chapitre 19, page 998 ! Et bien c'est parti pour un peu de lecture (je l'aurai vraiment parcouru dans tous les sens ce bouquin ;) )
06/06/2016, 20h59
elsuket
Bonjour Slaveak,

Merci pour vos commentaires sur le livre ! N'hésitez pas à nous poser des questions ou partager vos remarques.

Citation:

Est-il possible de faire passer un ordre DML en lots successifs ? en gros, tu INSERT les 10 000 premières lignes, tu COMMIT, tu passe au 10 000 suivantes ?

Oui, tout à fait. Deux exemples d'utilisation :

==> Ajouter une colonne non-NULLable à une table volumineuse : on découpe cela en 4 morceaux
1. Ajout de la colonne au type de données, NULLable
2. Mise à jour de la colonne à la valeur de la future contrainte de valuation par défaut, par lots de 10/100.000 lignes
3. Modification de la colonne pour la rendre non-NULLable
4. Ajout de la contrainte de valuation par défaut

==> Supprimer de nombreuses lignes d'une table : ce sont des tables qui tracent le comportement de l'application, donc on supprime celles dont la date d'insertion est antérieure à x mois par exemple.

Dans la première situation, cela permet effectivement de ne pas faire exploser la taille du fichier du journal des transactions.
En supposant une base de données dédiée à une telle mise à jour, je ne sais pas s'il est plus rapide de soumettre un seul ordre ou de pratiquer ce morcellement dans tous les cas.
Jusqu'ici au moins (cela doit faire 10 ans que j'ai écrit ce genre de choses pour la première fois), je n'ai eu que peu d'occasions de comparer, et il me semble qu'au pire c'est tout aussi long.

L'avantage de l'approche "morcelée" est que l'on peut donner un état d'avancement, puisqu'on sait combien de lignes sont à mettre à jour.
On peut aussi arrêter le lot, COMMITer la dernière transaction explicite, prendre une sauvegarde du fichier du journal des transactions si la base est dans un mode de récupération différent de SIMPLE (ça c'est quand on se rend compte en cours d'exécution que si on ne le fait pas, tout va péter :mouarf:). Sinon à la fin de chaque boucle, on peut tester à combien de pourcents est occupé le fichier du journal des transactions (DBCC SQLPERF(logspace) par exemple), et décider de prendre une sauvegarde du fichier du journal des transactions automatiquement ;)

J'ai oublié de vous dire que vous pourriez utiliser la DMV sys.dm_tran_database_transactions pour voir ce qui se passe.
Par exemple, on peut journaliser le résultat d'une requête dont le début serait :
Code:

1 2 3 4 SELECT * FROM sys.dm_tran_database_transactions WHERE database_id <> 2 -- exclut les transactions dans TempDB AND database_transaction_begin_time < DATEADD(minute, -5, GETDATE())
@++ ;)
07/06/2016, 10h13
Slaveak
Citation:

Envoyé par elsuket

Bonjour Slaveak,

Merci pour vos commentaires sur le livre ! N'hésitez pas à nous poser des questions ou partager vos remarques.

Ben alors déjà (et loin de moi l'idée de cirer des pompes hein !) Non merci à vous pour tout le boulot que vous avez fait ! Sincèrement j'apprends vraiment beaucoup de choses grâce à lui (et donc à vous) Que ce soit en formation ou en autodidacte via des blogs ou sites, je n'ai pas appris le quart de la moitié de ce que je lis à chaque fois que je plonge dedans. Je revois la conception de mes packages SSIS (que vous ne traitez pas dans ce livre) en fonction de ce que je lis sur SQL Server pour faire bcp pluis automatiser, me retirer des sources d'erreurs possible. J'avais pas exemple zappé que je pouvais mettre un getdate() en défaut dans mes colonnes d'horodatage, donc fini le composant "colonne dérivée" qui le fait dans SSIS et donc fini de s'apercevoir après coup que ma table n'est pas horodatée. Et ceci est valable au quotidien pour plein de petites choses ! Enfin bref je termine ma digression sur le sujet et je repasse sur mes problèmes de journaux de transaction.

Citation:

Envoyé par elsuket

Oui, tout à fait. Deux exemples d'utilisation :

==> Ajouter une colonne non-NULLable à une table volumineuse : on découpe cela en 4 morceaux
1. Ajout de la colonne au type de données, NULLable
2. Mise à jour de la colonne à la valeur de la future contrainte de valuation par défaut, par lots de 10/100.000 lignes
3. Modification de la colonne pour la rendre non-NULLable
4. Ajout de la contrainte de valuation par défaut

==> Supprimer de nombreuses lignes d'une table : ce sont des tables qui tracent le comportement de l'application, donc on supprime celles dont la date d'insertion est antérieure à x mois par exemple.

Alors dans mon cas, je pense que la méthode 1 est la plus adaptée. La méthode 2 est celle que j'utilise déjà, mais justement l'insertion pas date antérieur est déjà trop importante. Je n'ai donc pas d'autres choix que d'allier les deux.

Sinon je comprends l'idée conceptuelle derrière la méthode 1, par contre techniquement j'ai un peu du mal à voir comment faire. En gros l'idée est d'utiliser cette nouvelle colonne dans une procédure stockée qui va boucler un INSERT (ou un DELETE) avec comme itérateur cette colonne et de COMMIT une fois la boucle terminée, puis d'en relancer une autre, etc. ? C'est bien l'idée ?

Citation:

Envoyé par elsuket

Dans la première situation, cela permet effectivement de ne pas faire exploser la taille du fichier du journal des transactions.
En supposant une base de données dédiée à une telle mise à jour, je ne sais pas s'il est plus rapide de soumettre un seul ordre ou de pratiquer ce morcellement dans tous les cas.
Jusqu'ici au moins (cela doit faire 10 ans que j'ai écrit ce genre de choses pour la première fois), je n'ai eu que peu d'occasions de comparer, et il me semble qu'au pire c'est tout aussi long.

Effectivement c'est bien le but visé ! Et bien je vais tenter de me faire des procédures avec la méthode que vous décrivez.

Citation:

Envoyé par elsuket

L'avantage de l'approche "morcelée" est que l'on peut donner un état d'avancement, puisqu'on sait combien de lignes sont à mettre à jour.
On peut aussi arrêter le lot, COMMITer la dernière transaction explicite, prendre une sauvegarde du fichier du journal des transactions si la base est dans un mode de récupération différent de SIMPLE (ça c'est quand on se rend compte en cours d'exécution que si on ne le fait pas, tout va péter :mouarf:). Sinon à la fin de chaque boucle, on peut tester à combien de pourcents est occupé le fichier du journal des transactions (DBCC SQLPERF(logspace) par exemple), et décider de prendre une sauvegarde du fichier du journal des transactions automatiquement ;)

Oh comme c'est pas bête ça ! Et bien encore un autre truc à mettre en place sur ma road map !
Citation:
Envoyé par elsuket

J'ai oublié de vous dire que vous pourriez utiliser la DMV sys.dm_tran_database_transactions pour voir ce qui se passe.
Par exemple, on peut journaliser le résultat d'une requête dont le début serait :

Code:

1 2 3 4 SELECT * FROM sys.dm_tran_database_transactions WHERE database_id <> 2 -- exclut les transactions dans TempDB AND database_transaction_begin_time < DATEADD(minute, -5, GETDATE())

@++ ;)
Et bien je vais regarder cela avec attention.

Merci beaucoup pour tout ces conseils et idées (même si je vois ma todo list exploser !) et je vous tient au courant des avancées sur ce sujet.

++ :D

Citation:

merci à vous pour tout le boulot que vous avez fait ! Sincèrement j'apprends vraiment beaucoup de choses grâce à lui (et donc à vous) Que ce soit en formation ou en autodidacte via des blogs ou sites, je n'ai pas appris le quart de la moitié de ce que je lis à chaque fois que je plonge dedans

Merci à vous ;)

Citation:

Je revois la conception de mes packages SSIS (que vous ne traitez pas dans ce livre)

Effectivement ce n'est pas traité par le livre, mais il fait déjà 1208 pages, dont 366 sont à télécharger. Imaginez ce que cela aurait été si l'on avait traité SSIS ;)
Et alors ensuite, il y aura des lecteurs qui demanderont SSAS et SSRS, et là, le livre ne tient plus sur les genoux :mouarf:

Citation:

Alors dans mon cas, je pense que la méthode 1 est la plus adaptée. La méthode 2 est celle que j'utilise déjà, mais justement l'insertion pas date antérieur est déjà trop importante. Je n'ai donc pas d'autres choix que d'allier les deux.

Le procédé est le même dans les deux cas; seule l'instruction (sur ces exemples, UPDATE et DELETE) change.

Citation:

Sinon je comprends l'idée conceptuelle derrière la méthode 1, par contre techniquement j'ai un peu du mal à voir comment faire. En gros l'idée est d'utiliser cette nouvelle colonne dans une procédure stockée qui va boucler un INSERT (ou un DELETE) avec comme itérateur cette colonne et de COMMIT une fois la boucle terminée, puis d'en relancer une autre, etc. ? C'est bien l'idée ?

Effectivement. Un exemple pour le nettoyage d'une table dont la colonne qui supporte la clé primaire n'est pas un entier auto-incrémenté ou de type datetime, ou encore de type uniqueidentifier généré avec NEWSEQUENTIALID() :
Code:

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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 SELECT colonnePK INTO dbo.maTable_flush FROM dbo.maTable WHERE colonneDate < DATEADD(month, -3, GETDATE() -- Pour supporter l'ORDER BY ALTER TABLE dbo.maTable_flush ADD CONSTRAINT PK_maTable_flush PRIMARY KEY(colonnePK) WHILE EXISTS ( SELECT * FROM dbo.maTable_flush ) BEGIN BEGIN TRY BEGIN TRAN; WITH CTE AS ( SELECT TOP 10000 colonnePK FROM dbo.maTable_flush ORDER BY colonnePK ) DELETE FROM dbo.maTable FROM dbo.maTable AS TGT INNER JOIN CTE AS SRC ON TGT.colonnePK = SRC.colonnePK; WITH CTE AS ( SELECT TOP 10000 colonnePK FROM dbo.maTable_flush ORDER BY colonnePK ) DELETE FROM dbo.maTable_flush FROM dbo.maTable_flush AS TGT INNER JOIN CTE AS SRC ON TGT.colonnePK = SRC.colonnePK; COMMIT TRAN; END TRY END BEGIN CATCH DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10)) + ' - ' + ERROR_MESSAGE() , @err_svt int = ERROR_SEVERITY() , @err_stt int = ERROR_STATE(); IF XACT_STATE() <> 0 BEGIN ROLLBACK TRANSACTION; END RAISERROR(@err_msg, @err_svt, @err_stt); RETURN; END CATCH --DROP TABLE dbo.maTable_flush
J'ai mis 10000 comme taille de lot, mais vous pouvez l'adapter à vos besoins, mettre une variable, ...
Je vous laisse ajouter la sortie dans la console (PRINT ou RAISERROR('msg', 0, 1) WITH NOWAIT) et la gestion de la sauvegarde du fichier du journal des transactions ;)
Si la colonne qui supporte la clé primaire est un entier auto-incrémenté ou de type datetime, ou encore de type uniqueidentifier généré avec NEWSEQUENTIALID(), il y a une simplification ;)

@++ ;)

08/06/2016, 10h08
Slaveak

Ahhh super !

Merci beaucoup ! et bien je vais décortiquer tout ça de ce pas et je reviens vers vous, pour vous tenir au courant.

Effectivement la clé primaire est une colonne en INT auto incrémentée.