I. Problématique▲
Contrairement à MS-SQL, Sybase ASE n'a pas de commande dbcc shrinkdb. Cette faiblesse ne permet donc pas aisément de réduire la taille d'une base de données, et plus particulièrement de son journal de transactions.
Le dbcc shrinkdb de MS-SQL peut s'avérer coûteux en temps selon la volumétrie de la base traitée, et n'est de plus disponible que depuis la version 2000 du serveur : la méthode spécifiée ci-dessous peut donc être plus performante.
Dans la pratique, un certain nombre d'administrateurs de bases de données ne maîtrisent pas la problématique du journal de transactions. Celui-ci n'est que rarement vidé et croît donc de manière disproportionnée, ou est simplement mal taillé.
II. La réponse officielle▲
Mais que dit la documentation officielle à ce sujet ?
| Documentation | Commentaire | |
| 1 | que le journal de transaction doit être dissocié des autres segments | Ceci est exact. Sans cette condition sine qua non (hormis pour la base tempdb), les performances s'en ressentent, les sauvegardes incrémentielles ne sont pas possibles et toute tentative de récupération lors d'un crash disque s'en retrouve compliquée. |
| 2 | que la taille d'un journal de transactions doit être de 10 à 25 % de la taille des données | C'est exact pour de petites bases, mais pas pour de très grosses. La taille du journal se détermine selon l'utilisation que l'on fait de sa base (OLTP => plus de log que DSS). Il est rare d'attribuer plusieurs Go de log à bon escient à une base. |
| 3 | qu'il n'est pas possible de réduire la taille d'une base | … c'est pourtant ce que nous nous proposons de faire ! |
III. La solution officielle▲
Il faut donc passer par une recréation de base et de ses objets. Ce processus fastidieux peut être envisagé de diverses manières. En voici une parmi tant d'autres, minimisant l'utilisation du journal de transactions :
- créer une base de données plus petite sur le même serveur ;
- copier les tables d'une base à l'autre via select into (les identity sont maintenus, mais pas les triggers, ni les valeurs par défaut, ni les index)
$ isql -Usa -S SYBASE -D master -P xxx -o Transfert.sql << EOF
set nocount on
go
sp_dboption BaseNeuve, “select into”, true -- pour autoriser le fast bcp
go
use BaseNeuve
go
checkpoint -- pour activer la nouvelle option
go
-- Creation de la syntaxe du script de transfert
SELECT “SELECT * INTO ”+name+” FROM BaseVieille..”+name
FROM sysobjects
WHERE type=”U”
go
EOF
$ grep -E ‘SELECT * INTO' Transfert.sql >> Transfert2.sql
$ echo ‘go' >> Transfert2.sql
$ isql -Usa -S SYBASE -D BaseVieille -i Transfert2.sql -o output
$ rm Transfer*.sql- régénération de tous les autres objets de la base via defncopy ou tout autre outil derétrogénération, sans oublier les triggers, les valeurs par défaut et les index.
Voici, pour toute aide, la manière de régénérer les valeurs par défaut d'une base à l'autre :
$ isql -Usa -S SYBASE -D BaseVieille -P xxx -o Default.sql << EOF
set nocount on
go
SELECT ‘alter table ‘+object_name(id)+‘ modify ' + c.name + ‘ ‘ + co.text
FROM syscolumns c, syscomments co
WHERE c.id=co.id
AND co.text like ‘%DEFAULT%'
go
EOF
$ grep ‘alter' Default.sql >> Default2.sql
$ echo go >> Default2.sql
$ isql -Usa -S SYBASE -D NouvelleBase -P xxx -o Default2.sql << EOF
$ rm Default*.sql$ isql -Usa -S SYBASE -D BaseVieille -P xxx -o cpl.sql << EOF
set nocount on
go
SELECT ‘defncopy -Usa -P xxx -S SYBASE out ‘+ name+'.def BaseVieille'+ name
FROM sysobjects
WHERE type in ('TR','V','P','D')
go
EOF
$ grep ‘alter' Default.sql >> Default2.sql
$ echo go >> Default2.sql
$ isql -Usa -S SYBASE -D NouvelleBase -P xxx -o Default2.sql << EOF
$ rm Default*.sqlIV. La solution rapide par suppression de(s) segment(s)▲
Soulignons tout d'abord que cette solution n'est officiellement pas supportée par Sybase ou Microsoft.
Par souci de simplification, je présente ici la méthode pour la suppression d'une partie de journal de transactions, mais cela peut être fait pour un segment de données, sous réserve de quelques points que je spécifierai plus loin.
Voici les plages de journaux de transactions utilisées par ma base :
SELECT name, lstart, segmap, size, unreservedpgs
FROM master..sysusages, master..sysdevices
WHERE dbid=db_id(“MaBase”)
AND vstart between low and high
AND segmap&4=4
ORDER BY lstart
name lstart segmap size unreservedpgs
------------------------------ ----------- ----------- ----------- -------------
log2 7680 4 1024 1027
log1 8704 4 2560 2550
log1 11264 4 1536 1514
log2 14336 4 3584 3570On va donc essayer de vider des segments non utilisés, et ceci en commençant par le bas de la liste.
Commençons par une sauvegarde de notre base de données via dump database, ainsi qu'une copie de la base master..sysusages.
dump database MaBase to ‘/users/db/dump/MaBase.dmp'
$ bcp master..sysusages out /users/db/dump/sysusages.bcp -c -Usa -Pxxx -S SYBASEVidons ensuite le journal de transactions :
dump tran MaBase with no_log
goPuis réaffichons l'utilisation des segments :
SELECT name, lstart, segmap, size, unreservedpgs
FROM master..sysusages, master..sysdevices
WHERE dbid=db_id(“MaBase”)
AND vstart between low and high
AND segmap&4=4
ORDER BY lstart
name lstart segmap size unreservedpgs
------------------------------ ----------- ----------- ----------- -------------
log2 7680 4 1024 1027
log1 8704 4 2560 2550
log1 11264 4 1536 1514
log2 14336 4 3584 3570Dans les versions pré-12, il nous suffisait d'avoir une égalité entre la taille et le nombre de pages non réservées pour pouvoir en déduire que des segments n'étaient pas du tout utilisés. Ce n'est plus du tout le cas dès la version 12, la colonne unreservedpgs ne faisant pas un calcul correct.
Nous allons donc exécuter un balayage de la table syslogs (le journal de transactions) à l'aide de la commande tablealloc afin de déterminer la localisation des pages d'allocations, et de ce fait des pages allouées à cette table.
dbcc traceon(3604)
go
dbcc tablealloc (syslogs)
go
The default report option of OPTIMIZED is used for this run.
The default fix option of NOFIX is used for this run.
***************************************************************
TABLE: syslogs OBJID = 8
INDID=0 FIRST=12341 ROOT=8557 SORT=0
Data level: 0. 4902 Data pages allocated and 613 Extents allocated.
TOTAL # of extents = 613
Alloc page 7680 (# of extent=32 used pages=256 ref pages=256)
Alloc page 7936 (# of extent=32 used pages=256 ref pages=256)
Alloc page 8192 (# of extent=32 used pages=256 ref pages=256)
Alloc page 8448 (# of extent=14 used pages=112 ref pages=110)
Alloc page 12288 (# of extent=26 used pages=203 ref pages=203)
Alloc page 12544 (# of extent=32 used pages=256 ref pages=256)
Alloc page 14336 (# of extent=32 used pages=256 ref pages=256)
Alloc page 14592 (# of extent=32 used pages=256 ref pages=256)
Alloc page 14848 (# of extent=32 used pages=256 ref pages=256)
Alloc page 15104 (# of extent=32 used pages=256 ref pages=256)
Alloc page 15360 (# of extent=32 used pages=256 ref pages=256)
Alloc page 15616 (# of extent=32 used pages=256 ref pages=256)
Alloc page 15872 (# of extent=32 used pages=256 ref pages=256)
Alloc page 16128 (# of extent=32 used pages=256 ref pages=256)
Alloc page 16384 (# of extent=32 used pages=256 ref pages=256)
Alloc page 16640 (# of extent=32 used pages=256 ref pages=256)
Alloc page 16896 (# of extent=32 used pages=256 ref pages=256)
Alloc page 17152 (# of extent=32 used pages=256 ref pages=256)
Alloc page 17408 (# of extent=32 used pages=256 ref pages=256)
Alloc page 17664 (# of extent=32 used pages=256 ref pages=256)
Total (# of extent=616 used pages=4923 ref pages=4921) in this database
Statistical information for this run follows:
Total # of pages read = 4902
Total # of pages found cache = 2548
Total # of physical reads = 296
Total # of saved I/O = 2058
Size of I/O used = 8 pages
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.La dernière page d'allocation est donc la page 17664.
Déterminons maintenant quels segments peuvent être supprimés, supprimons-les et redémarrons notre serveur :
sp_configure allow_updates,1
go
DECLARE @MaxSeg int
-- suppression du cas d'un segment mixte log/data
SELECT @MaxSeg = Max(lstart)
FROM sysusages
WHERE dbid=db_id(“MaBase”)
AND segmap&4=4
AND segmap > 4
-- destruction
DELETE sysusages
WHERE dbid=db_id(“MaBase”)
AND lstart >17664
AND lstart > @MaxSeg
AND segmap=4
AND
go
sp_configure allow_updates,0
go
shutdownVoilà ! La réduction du journal s'est effectuée. Pour s'assurer que tout s'est bien passé, redémarrez votre serveur et exécutez les commandes dbcc d'usage :
use MaBase
go
dbcc traceon(3604)
go
dbcc checkcatalog
go
-- Les 2 commandes suivantes sont avantageusement remplacées par dbcc checkcatalog si vous l'avez configuré correctement
dbcc checkdb
dbcc checkalloc
go