Réduction de la taille d'une base Sybase ASE ou MS-SQL

Fabien Celaia

- Titre : Réduction de la taille d'une base Sybase ASE ou MS-SQL

- Auteur : Fabien Celaia

- Parution : 11 juin 2004

- Mise à jour : 15 janvier 2006

- 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. Problématique

- II. La réponse officielle

- III. La solution officielle

- IV. La solution rapide par suppression de(s) segment(s)

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, &#8220;select into&#8221;, 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 &#8220;SELECT * INTO &#8221;+name+&#8221; FROM BaseVieille..&#8221;+name

FROM sysobjects

WHERE type=&#8221;U&#8221;

go

EOF

$ grep -E &#8216;SELECT * INTO' Transfert.sql >> Transfert2.sql

$ echo &#8216;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 &#8216;alter table &#8216;+object_name(id)+&#8216; modify ' + c.name + &#8216; &#8216; + co.text

FROM syscolumns c, syscomments co

WHERE c.id=co.id

AND co.text like &#8216;%DEFAULT%'

go

EOF

$ grep &#8216;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 &#8216;defncopy -Usa -P xxx -S SYBASE out &#8216;+ name+'.def BaseVieille'+ name

FROM sysobjects

WHERE type in ('TR','V','P','D')

go

EOF

$ grep &#8216;alter' Default.sql >> Default2.sql

$ echo go >> Default2.sql

$ isql -Usa -S SYBASE -D NouvelleBase -P xxx  -o Default2.sql << EOF

$ rm Default*.sql

IV - 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(&#8220;MaBase&#8221;)

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           3570

On 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 &#8216;/users/db/dump/MaBase.dmp'

$ bcp master..sysusages out /users/db/dump/sysusages.bcp -c -Usa -Pxxx -S SYBASE

Vidons ensuite le journal de transactions :

dump tran MaBase with no_log

go

Puis réaffichons l'utilisation des segments :

SELECT name, lstart, segmap, size, unreservedpgs

FROM master..sysusages, master..sysdevices

WHERE dbid=db_id(&#8220;MaBase&#8221;)

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            3570

Dans 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(&#8220;MaBase&#8221;)

AND segmap&4=4

AND segmap > 4

-- destruction

DELETE sysusages

WHERE dbid=db_id(&#8220;MaBase&#8221;)

AND lstart >17664

AND lstart > @MaxSeg

AND segmap=4

AND

go

sp_configure allow_updates,0

go

shutdown

Voilà ! 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