Discussion :

[6bil1]

Bonjour,

j'ai un probleme avec mon journal de transaction. Il est énorme.

Agr_log.ldf fait 250 Go (Journal de transaction)
Agr_Data.mdf fait 155 Go (Les données)

Tous les soirs, je fais une sauvegarde de la base et du journal de transaction.
La base est en mode de récuperation complet


En cherchant sur le net, j'ai trouvé des commandes ->

Voici les commandes que j'ai passé :

CHECKPOINT
BACKUP LOG agresso WITH TRUNCATE_ONLY
BACKUP TRANSACTION agresso WITH NO_LOG

USE Agresso
GO
DBCC SHRINKFILE ('agrLog')
GO

(La commande se passe bien)

ou encore

USE Agresso
GO
DBCC SHRINKFILE ('agrLog', 200)
GO

(La commande se passe bien aussi) Il me sort :
7 2 26522560 128 26522560 128





Dans un plan de maintenance, j'ai fait reduire la base, mais rien.Ca ne veut pas se réduire.

je souhaite passer le journal à 200 mo.

[elsuket]

Bonjour,

C'est une erreur que de vouloir réduire la taille d'une base de données.
En effet, lorsque SQL Server décide d'augmenter la taille d'un fichier, il doit effectuer un nombre importants d'accès disque. Cela crée une contention importante au niveau des transaction, puisque comme vous vous en doutez, les accès disques sont au moins 1000 fois plus lents qu'un accès en RAM.

Lorsque vous réduisez une base de données, vous supprimez les pages que SQL Server s'est échiné à allouer sur le disque, et qu'il devra ré-allouer tôt ou tard, donc vous créez vous-même de la contention

C'est pourquoi la manœuvre de réduction de la base de données ne doit être réservée qu'aux cas d'urgence, du style manque d'espace disque.

Enfin, un grossissement des fichiers de base de données indique souvent une mauvaise estimation du dimensionnement des données.
A l'allocation des pages, vous créez de la fragmentation dans vos fichiers, je vous laisse imaginer les conséquences que cela a sur les performances globales de votre base de données
Taillez donc vos fichiers de sorte qu'ils n'aient pas ou peu à grossir, en estimant la taille des données à stocker sur 3 ans ou un peu plus

La troncature du fichier du journal des transactions ne conduit donc pas à une réduction de la taille de celui-ci, mais seulement à un "nettoyage" journaux virtuels qui sont contenus dans le fichier de journal des transactions de votre BD.

Regardez la colonne log_reuse_wait_desc de la table système sys.databases pour votre base de données, et dites-nous quelle valeurs elle prend

Si vous souhaitez que votre fichier de journal des transaction ne grossisse pas, taillez le correctement et effectuez des sauvegardes de celui-ci de façon plus fréquente.

Enfin, le mode de restauration d'une base de données est à choisir avec précaution, suivant la quantité de données que vous pouvez vous "permettre" de perdre en cas de crash. Dans votre cas, est-il acceptable de perdre une journée de données ? Si ce n'est pas le cas, cela vous fait une raison supplémentaire pour effectuer des sauvegardes du journal des transaction plus fréquemment

Votre base de données est-elle OLTP ou accédée seulement en lecture seule ?

@++

[6bil1]

Voici le résultat de la commande SELECT log_reuse_wait_desc FROM sys.DATABASES :

NOTHING
NOTHING
NOTHING
NOTHING
NOTHING
NOTHING
ACTIVE_TRANSACTION

La base est OLTP. C'est une application metier qui "dirige" cette base.

j'ai contacté le fournisseur et en effet, il em confirme que 250 Go de journal de transaction c'est énorme. Il conseil 200 Mo

De plus, je commence à avoir un problème de place sur mon serveur...

[SQLpro]

j'ai contacté le fournisseur et en effet, il em confirme que 250 Go de journal de transaction c'est énorme. Il conseil 200 Mo

C'est un conseil hautement stupide d'un fournisseur qui ne maîtrise vraiment pas l'administration de bases de données.

Comme on vous l'as dit, réduire à si peu un journal de transaction est d'une parfaite stupidité. Lisez l'article que j'ai écrit à ce sujet :
1) http://blog.developpez.com/sqlpro/p5...fichiers-et-t/
en particulier paragraphe intitulé "Pire ! C'est possible..."
2) http://sqlpro.developpez.com/cours/sqlserver/log/
en cas d'urgence, comment réduire le JT

Cependant sachez qu'un JT ne peut se réduire à moins de sa taille de départ...

A +

[6bil1]

Justement, je me suis basé sur votre article pour essayer de réduire le journal mais ca n'a rien fait !
J'ai bien suivi votre article, c'est pour ca qu'au début, j'ai posté les commandes que j'ai passé.

[6bil1]

Si je passe la commande

DBCC SQLPERD (LOGSPACE)
j'ai ce résultat :

Databasename Log Size MB Log Space Used statut
Agresso 233626,3 98,43719 0



Donc, je lance la commande BACKUP LOG Agresso WITH TRUNCATE_ONLY

et ensuite dbcc shrinkfile (AgrLog)

Et j'ai le meme résultat ! On dirait qu'il ne vide pas le journal... Il grossit

[elsuket]

Faites-vous des sauvegardes régulières de votre fichier du journal des transactions comme proposé par elsuket ?

Code :

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

SELECT log_reuse_wait_desc FROM sys.DATABASES

Votre requête devrait plutôt être :

Code :

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

1
2
3
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'maBaseDeDonnees'

Sans cela le résultat que vous donnez n'est pas interprétable ...
Avez-vous regardé les valeurs que prend la colonne log_reuse_wait_descpour votre base de données dans la journée ?

@++

[SQLpro]

Quelle version de SQL? Avez vous du log shipping ? Du mirroring ??
N'y a t-il pas des transactions en cours non encore terminées (genre programmation spaghetti) ?
Pour le savoir faites un :
DBCC OPENTRAN

A +

[6bil1]

Bonjour et merci de vos réponses.

la requête :

Code :

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

1
2
3
SELECT name, log_reuse_wait_desc
FROM sys.DATABASES
WHERE name = 'maBaseDeDonnees'

me donne :
Agresso ACTIVE_TRANSACTION

Le fichier journal des transactions est sauvegardé tous les soirs à 20H00 -> Un plan de maintenance qui sauvegarde la base et ensuite le journal de transaction.

J'ai SQLServer 2005
Le log shipping je ne sais pas ce que c'est...
Et le mirroring, non je n'en fais pas.

La commande DBCC OPENTRAN me donne comme résultat :


Informations de transaction pour la base de données 'Agresso'.

Code :

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

1
2
3
4
5
6
7
8
9
 
Plus ancienne transaction active*:
    SPID (ID du processus serveur) : 72
    UID (ID utilisateur)*: -1
    Nom            : user_transaction
    Numéro de séquence d'enregistrement      : (448087:6997:1)
    Heure de début*: mai  7 2009  5:58:08:363PM
    SID      : 0xe143d3bde8ad9747b066235d889731fe
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

[SQLpro]

Voila donc la raison de l'impossibilité de réduire votre JT : vous avez une transaction encore active qui a démarré le 7 mai 2009 à 17:58:08:363 et n'est pas terminé. C'est le processus 72 qui travaille cette transaction...
A vous de voir ce dont il s'agit et éventuellement de ROLLBACKER ou COMMITER cette tâche !

Je soupçonne bien évidemment un codage catastrophique de votre application...

A +

[elsuket]

Vous pourrez trouver le texte de la commande SQL à l'origine de cette transaction restée ouverte à l'aide des vue des gestion dynamiques de SQL Server 2005 :

Code :

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

1
2
3
4
SELECT ESQLT.text
FROM sys.dm_exec_requests ER (nolock)
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) ESQLT
WHERE ER.session_id = 72

Je vous laisse le soin de choisir les autres colonnes

@++

[6bil1]

Lorsque je veux "tuer" la requete, par sql manager, ca ne marche pas, le processus 72 ne veut pas se terminer.


Je viens d'executer la commance

Code :

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

1
2
3
4
SELECT ESQLT.text
FROM sys.dm_exec_requests ER (nolock)
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) ESQLT
WHERE ER.session_id = 72

j'ai comme résultat :

Code :

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

1
2
 
INSERT INTO asushdheader ( apar_gr_id , apar_id , apar_id_ref , apar_name , apar_once , apar_type , bank_account , bonus_gr , cash_delay , clearing_code , client , collect_flag , comp_reg_no , control , country_code , credit_limit , currency , currency_set , description , disc_code , expired_date , ext_apar_ref , fac_short_set , factor_short , foreign_acc , intrule_id , invoice_code , language , last_update , main_apar_id , message_text , pay_delay , pay_method , pay_method_set , pay_temp_id , postal_acc , priority_no , short_name , status , swift , tax_code , tax_set , tax_system , tc_set , terms_id , terms_set , trans_date , user_id , vat_reg_no , wf_state , change_seq_no , change_status ) SELECT apar_gr_id , apar_id , apar_id_ref , apar_name , apar_once , apar_type , bank_account , bonus_gr , cash_delay , clearing_code , client , collect_flag , comp_reg_no , control , country_code , credit_limit , currency , currency_set , description , disc_code , expired_date , ext_apar_ref , fac_short_set , factor_short , foreign_acc , intrule_id , invoice_code , language , last_update , main_apar_id , message_text , pay_delay , pay_method , pay_method_set , pay_temp_id , postal_acc , priority_no , short_name , status , swift , tax_code , tax_set , tax_system , tc_set , terms_id , terms_set , trans_date , user_id , vat_reg_no , wf_state , 40676 , 'D' FROM asushdheader WHERE client = '11' AND apar_id = '46744' AND last_update = (SELECT max(last_update ) FROM asushdheader WHERE client = '11' AND apar_id = '46744' )

[elsuket]

Liez sys.dm_exec_requests (ER) avec sys.dm_exec_sessions (ES) et sys.dm_exec_connections (EC) sur le session_id pour connaître l'adresse IP de la machine (EC.client_net_address et ES.host_name).
Vous pouvez également utiliser ER.start_time pour savoir depuis combien de temps la requête est exécutée ...

Quelle erreur obtenez-vous quand vous faites KILL 72 ?
Le processus 72 n'est-il pas bloqué par un autre ? (colonne blocking_session_id de ER > 0) ?

@++

[SQLpro]

En tout cas c'est clair la base de données visée par cet ordre SQL est un modèle du genre...
52 colonnes dans une table (en audit je flingue à partir de 20)
Utilisation de 5 mots réservés de SQL sans utilisation des guillemets
Et je parierais qu'il n'y a presque aucun index et en particulier pas sous la combinaisons des colonnes (client, apar_id, last_update)

A +

[6bil1]

La commande KILL 72 marche parfaitement...Je suis passé par le manager... C'est surement la cause.

Je suis retourné dans une configuration "normale". ma sauvegarde ne prend plus 300 Go.



Merci à vous !

je vais remonter le problème a l'éditeur.