Le journal de transactions et les seuils d'alerte sous ASE

Fabien Celaia

- Titre : Le journal de transactions et les seuils d'alerte sous ASE

- Auteur : Fabien Celaia

- Parution : 7 janvier 2006

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2013 Fabien Celaia. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Préface

- II. Principe de fonctionnement

- III. Impact sur les sauvegardes

- III-A. Sauvegarde de base de données complète uniquement

- III-B. Sauvegarde de la base de données et des journaux de transactions

- IV. Vidange manuelle

- V. Vidange automatique

I - Préface

La plupart des SGBDR dignes de ce nom se doivent de maintenir leurs données consistantes en quelque moment que ce soit. Il faut donc un processus permettant

- la récupération des données et le retour en un état transactionnel consistant en cas de crash au milieu d'activité ;

- le retour en arrière d'une transaction au cas où une erreur apparaîtrait et la récupération des anciennes valeurs ;

- la sauvegarde consistante de la base de données pendant la période d'activité ;

- la réplication des modifications effectuées dans des environnements distribués.

C'est donc le journal de transactions qui sera le garant de toutes ces différentes fonctionnalités.

Sous Sybase ASE, le journal de transactions est en fait stocké dans une table propre à chaque base de données : la table système syslogs.

Elle n'est pas utilisable en clair et est remplie séquentiellement.

C'est cette table, et plus particulièrement la gestion de la place qu'elle utilise sur disque qui pose un certain nombre de problèmes aux DBA en herbe.

II - Principe de fonctionnement

Prenons une transaction bancaire consistant à débiter et créditer deux comptes.

La transaction doit être atomique : soit les deux mouvements passent, soit aucun ne doit passer, soit…

- supprimer CHF 1000.-- du compte A ;

- ajouter CHF 1000.-- au nouveau compte B ;

- si tout s'est bien passé, valider, sinon invalider.

Ce qui s'écrit, en T-SQL

BEGIN TRAN T2

UPDATE CompteA SET Valeur=Valeur-1000 WHERE Noclient=999

if @@error <> 0

ROLLBACK

insert into CompteB (NoClient, Valeur) values (999,1000)

if @@error <> 0

ROLLBACK

COMMIT

Que se passe-t-il au niveau du journal de transactions ?

III - Impact sur les sauvegardes

Il faut ici déterminer quel type de sauvegarde vous effectuez.

III-A - Sauvegarde de base de données complète uniquement

Dans le cas où vous n'effectuez que des sauvegardes de base de données, vous devez tenir compte des points suivants

- vous acceptez implicitement la perte de données qui pourrait se produire depuis votre dernière sauvegarde ;

- vous vous interdisez la possibilité de revenir en arrière de quelques transactions en cas de DELETE/UPDATE malvenus.

Cela étant posé, cette façon de faire offre les intérêts suivants :

- simplification de la gestion des journaux de transactions ;

- simplification de la gestion des sauvegardes.

Dans ce cas, il vous suffit de mettre l'option "trunc log on checkpoint" à votre base de données. Ce faisant, la partie inactive du journal de transactions sera nettoyée à chaque fois qu'un checkpoint (à savoir une écriture des blocs de la mémoire aux disques) s'effectuera.

USE master

GO

sp_dboption Votrebase, 'trunc log', true

GO

Dans le même ordre d'idée, vous pouvez déterminer ce qui se passera si une transaction venait à remplir le journal. Vous pouvez choisir de tout arrêter tant que de la place ne se fait pas... dans ce cas, toute modification de cette base sera gelée. C'est l'option par défaut.

Sinon vous pouvez opter de tuer la transaction saturant le log automatiquement :

USE master

GO

sp_dboption Votrebase, 'abort tran on log full', true

GO

III-B - Sauvegarde de la base de données et des journaux de transactions

IV - Vidange manuelle

Pour vidanger manuellement un journal de transactions plein, on doit exécuter la commande suivante :

DUMP TRAN MaBase WITH TRUNCATE_ONLY

Cette commande va tronquer toutes les transactions fermées apparaissant dans le journal, jusqu'à la première transaction ouverte.

L'ASE doit cependant écrire dans ledit journal ce qu'il est en train de faire… ce qui pose un sérieux problème si le journal est totalement saturé. Dans ce cas, il y a possibilité de lui demander de tronquer le log sans spécifier quoi que ce soit dans le journal via

DUMP TRAN MaBase WITH NOLOG

C'est une méthode assez brutale, à n'utiliser qu'en ultime recours, car si un problème matériel venait à apparaître à ce moment, la récupération automatique de la base pourrait être problématique : l'ASE essayerait de remonter un journal de transactions qu'il ne serait pas à même de trouver ! En effet, l'ASE cherchera à remonter une transaction dont l'ID se trouve dans master..syslogshold… et cette transaction ne sera plus présente dans Votrebase..syslogs !

Il y a encore un cas de figure problématique : c'est celui de la transaction qu'un développeur aurait oublié de fermer.

Dans ce cas, le journal de transactions se retrouve saturé par une seule transaction ouverte, donc impossible à vidanger.

La solution consiste alors à tuer toutes les transactions ouvertes de la base problématique : ce faisant, la session problématique sera tuée et sa transaction rollbackée… et donc supprimable.

La requête suivante vous génère les ordres SQL à exécuter pour tuer vos sessions. Il faut donc rejouer l'output de cette requête.

SELECT 'KILL '+convert(char(5), spid)

from master..sysprocesses

where dbid=db_id('MaBase')

… puis réessayer le DUMP TRAN.

Un autre cas de bord viendrait du fait que votre base aurait été une fois une base répliquée, utilisée par Sybase Replication Server ou SQL Remote. Dans ce cas, un deuxième point de troncature, nommé LTM (Log Transaction Mark) est présent et suit, avec une certaine latence, le point de troncature… et dans ce cas précis, le dump tran ne peut sectionner une transaction n'ayant pas passé dans la moulinette de la réplication.

Il est assez aisé de se rendre compte qu'un LTM existe en testant la commande suivante : un LTM retourne une adresse supérieure à 0.

use VotreBase

GO

dbcc gettrunc

GO

Dans ce cas, il y a un moyen de supprimer ce point de troncature, tout en étant conscient de l'impact que cela pourrait avoir dans le cas où votre base serait réellement une base source de réplication !

USE VotreBase

GO

dbcc settrunc('ltm', 'ignore')

GO

... et dans le cas où ce problème se passerait en phase de récupération (et donc que la base ne pourrait être ouverte)

use VotreBase

GO

dbcc dbrepair (VotreBase, ltmignore)

GO

V - Vidange automatique

Il y a possibilité de créer une procédure stockée sp_thresholdaction.

Soit il vous est possible de la créer dans la base sybsystemprocs, et dans ce cas elle sera active pour toutes vos bases.

Soit il vous est possible de la créer dans une base spécifique, et dans ce cas elle sera active dans cette base uniquement, en suppléant au besoin une sp_thresholdaction créée dans sybsystemprocs.

Voici un exemple de procédure de seuil. Notez que vos pouvez modifier à votre guise le corps de la procédure, mais ni son nom, ni ses paramètres d'entrée :

create procedure sp_thresholdaction

@dbname varchar(30),

@segmentname varchar(30),

@space_left int,

@status int

as

dump transaction @dbname with truncate_only

Il va sans dire que cet exemple est le plus basique qu'il soit. Nous pouvons l'enrichir à notre guise :

CREATE PROC sp_thresholdaction (

@dbname varchar(30),

@segmentname varchar(30),

@space_left int,

@status int )

AS

BEGIN

declare @msg  varchar(80),

@date_actuelle  datetime,

@nom_fichier  varchar(100),

@date_fichier  varchar(20),

@rep_sauve_log  varchar(40)

select @rep_sauve_log = C:\backup\log\'

select @date_actuelle = getdate()

select @date_fichier =

convert(varchar(4),datepart(YYYY,@date_actuelle)) +

convert(varchar(2),datepart(MM,@date_actuelle)) +

convert(varchar(2),datepart(DD,@date_actuelle)) +'_'+

convert(varchar(2),datepart(HH,@date_actuelle)) +

convert(varchar(2),datepart(MI,@date_actuelle))

select @nom_fichier = @rep_sauve_log + @dbname + @date_fichier + '.trn',

@msg = 'ATTENTION: Seuil de la dernière chance atteint sur la base ' + @dbname + ', segment ' + @segmentname

print @msg

if @segmentname = 'logsegment'

dump tran @dbname to @nom_fichier

return

END

Relevons que cette procédure est donc lancée automatiquement par l'ASE.

Il est par contre possible d'ajouter des seuils à n'importe quel niveau et sur n'importe quel segment en l'activant via sp_addthreshold.

Voici donc un exemple pour ajouter un seuil sur un segment d'index de la base MonTest.

sp_addthreshold @dbname='MonTest', @segname='index_seg', @freespace=10, @proc_name='sp_thresholdaction'