Créer, gérer et supprimer une base de données

Une base de données gère un ensemble de tables système et des tables utilisateurs. Les informations contenues dans
ces tables système représentent, entre autres, la définition des vues, des index, des procédures, des fonctions, des
utilisateurs et des privilèges. Les tables utilisateurs vont contenir les informations saisies par les utilisateurs.

1. Créer une base de données

La création d’une base de données est une étape ponctuelle, réalisée par un administrateur SQL Server. Avant de
tenter de créer une base de données, il est important de définir un certain nombre d’éléments de façon précise :

● le nom de la base de données qui doit être unique sur le serveur SQL,

● la taille de la base de données,

● les fichiers utilisés pour le stockage des données.

Pour créer une nouvelle base de données, SQL Server s’appuie sur la base Model. Cette base Model contient tous les
éléments qui vont être définis dans les bases utilisateurs. Par défaut, cette base Model contient les tables système. Il
est cependant tout à fait possible d’ajouter des éléments dans cette base. Toutes les bases utilisateurs créées par la
suite disposeront de ces éléments supplémentaires.

Une base peut être créée de deux façons différentes :

● par l’intermédiaire de l’instruction Transact SQL CREATE DATABASE ;

● par l’intermédiaire de SQL Server Management Studio.

Une base de données est toujours composée au minimum d’un fichier de données principal (extension mdf) et d’un
fichier journal (extension ldf). Des fichiers de données secondaires (ndf) peuvent être définis lors de la création de la
base ou bien ultérieurement.

Cette opération de création de base de données affecte la base Master. Une sauvegarde de cette base système
s’avère donc nécessaire pour être en mesure de travailler avec la nouvelle base suite à une restauration.

Les informations concernant les fichiers de données sont enregistrées dans la base Master ainsi que dans le fichier
primaire de la base de données.

a. La syntaxe Transact SQL

CREATE DATABASE nomBaseDeDonnées

[ ON
[PRIMARY] [ <spécificationFichier> [,n]]
[LOG ON < spécificationFichier > [,n]]
]
[ COLLATE classement ]
[;]

Avec pour spécificationFichier les éléments de syntaxe suivants :

(NAME = nomLogique,
FILENAME = ’cheminEtNomFichier’ [,SIZE = taille
[KB|MB|GB|TB]]
[,MAXSIZE={tailleMaximum[KB|MB|GB|TB]|UNLIMITED}
] [,FILEGROWTH = pasIncrement [KB|MB|GB|TB|%]]
) [,n]

PRIMARY
Permet de préciser le premier groupe de fichiers de la base. Ce groupe est obligatoire, car l’ensemble des tables
système est obligatoirement créé dans le groupe primary. Si le mot clé PRIMARY est omis, le premier fichier de
données précisé dans la commande CREATE DATABASE correspond obligatoirement au fichier primaire, il porte
normalement l’extension mdf.

NAME

Cet attribut, obligatoire, permet de préciser le nom logique du fichier. Ce nom sera utilisé pour faire des
manipulations sur le fichier via des commandes Transact SQL ; on pense notamment aux commandes DBCC ou à la
gestion de la taille des fichiers.

FILENAME

Spécification du nom et emplacement physique du fichier sur le disque dur. Le fichier de données est toujours créé
sur un disque local du serveur.

SIZE

Attribut optionnel qui permet de préciser la taille du fichier de données. La taille par défaut est de 1 Mo et la taille
minimale d’un fichier est 512 Ko aussi bien pour le journal que pour les fichiers de données. La taille peut être
précisée en kilo­octets (Kb) ou en mégaoctets (Mb, par défaut). La taille du premier fichier de données doit être au
moins égale à celle de la base Model.

MAXSIZE

Cet attribut optionnel, permet de préciser la taille maximale en Méga octets (par défaut) ou en kilo octets que peut
prendre le fichier. Si rien n’est précisé, le fichier grossira jusqu’à saturation du disque dur.

FILEGROWTH

Il s’agit de préciser le facteur d’extension du fichier. La taille de chacune des extensions peut correspondre à un
pourcentage (%), à une taille en Méga octets (par défaut) ou en Kilo octets. Si on précise la valeur zéro, le facteur
d’extension automatique du fichier est nul et donc la taille du fichier ne change pas automatiquement. Si le critère
FILEGROWTH est omis, la valeur par défaut est de 1 Mo pour les fichiers de données et de 10 % pour les fichier
journaux. La taille des extensions est toujours arrondie au multiple de 64 Ko (8 blocs) le plus proche.

Toutes les options de l’instruction CREATE DATABASE ne sont pas exposées ici. Seules les options les plus
couramment utilisées lors de la création d’une nouvelle base le sont.
b. Utilisation de SQL Server Management Studio

Il est également possible de créer une base de données de façon graphique depuis SQL Server Management
Studio. Il faut alors sélectionner Nouvelle base de données depuis le menu contextuel associé au nœ ud Bases de
données depuis l’explorateur d’objets, comme illustré ci­dessous.

SQL Server Management Studio présente alors la boîte de dialogue des propriétés de la base en mode création.
Après avoir complété les options comme le nom de la base et le nom et l’emplacement des fichiers, il est possible de
demander la création de la base.
 Cette boîte de dialogue permet à un utilisateur averti de créer rapidement et simplement une base de données,
tout en conservant la maîtrise de tous les paramètres.

Il est possible de créer des fichiers sur des partitions brutes. Cependant, cette technique n’offre qu’un faible gain de
performance par rapport à la création de fichiers sur des partitions formatées NTFS. De plus, les fichiers créés sur
des partitions brutes ne peuvent pas être manipulés par le système de fichiers (notamment dans le cadre des
sauvegardes base arrêtée) et il ne peut y avoir, bien sûr, qu’un seul fichier par partition.

Les fichiers de base de données ne doivent pas être créés sur des partitions compressées. Dans certains cas
extrêmes, il est possible de placer les fichiers secondaires sur des partitions compressées. Les fichiers doivent alors
être en lecture seule. Par contre, les fichiers journaux ne doivent en aucun cas être définis sur une partition
compressée.

2. Gérer une base de données

Lors de la gestion d’une base de données, plusieurs critères sont à prendre en compte. Dans cette section, la gestion
de l’espace utilisé par les fichiers physiques qui constituent la base de données est abordée. Les points principaux
qui concernent la gestion des fichiers sont : l’accroissement dynamique ou manuel des fichiers, l’ajout de nouveaux
fichiers, la réduction de la taille des fichiers.

a. Augmenter l’espace disque disponible pour une base de données

Les fichiers de données et les fichiers journaux stockent de l’information. Comme la base contient normalement de
plus en plus d’informations, à un moment donné ces fichiers seront complets. Il se posera alors le problème de
savoir où prendre la place.
Les différentes méthodes exposées ci­dessous pour augmenter l’espace de stockage dont dispose la base sont
complémentaires car chaque méthode possède ses avantages et ses inconvénients.

Fichieràaccroissementdynamique

Lors de la création de la base, il est possible de fixer un certain nombre de critères concernant la taille maximale de
fichiers (MAXSIZE) et le taux d’accroissement (FILEGROWTH). Si ces options sont omises la taille maximale est infinie
et le taux d’accroissement est de 10 % pour les journaux et 1 Mo pour les fichiers de données.

En utilisant des fichiers à croissance dynamique, le serveur ne sera jamais bloqué par la taille du fichier sauf
saturation du disque ou taille maximale atteinte.

Le facteur d’accroissement permet de fixer la taille de tous les ajouts. Cette taille doit être fixée de façon optimale,
en prenant en compte le fait qu’un facteur d’accroissement trop petit introduit beaucoup de fragmentations
physiques du fichier et les demandes d’extension du fichier sont fréquentes, tandis qu’un facteur d’accroissement
trop grand nécessite une charge de travail importante de la part du serveur lorsque celui­ci met en place la
structure de blocs de 8 Ko à l’intérieur du fichier.

Si le taux d’accroissement est fixé à zéro, le fichier ne pourra pas grandir dynamiquement.

L’accroissement du fichier possède toujours une taille qui est multiple de 64 Ko (taille d’une extension).

Si le paramétrage des fichiers permet de gérer automatiquement la croissance des fichiers, cette solution présente
tout de même le désavantage du fait qu’il n’est pas possible de maîtriser quand cet accroissement aura lieu. Si cette
opération intervient en pleine charge de travail, elle risque de ralentir le serveur.

Par contre, si le paramétrage de l’accroissement automatique des fichiers est réalisé, cela permet en cas de
problème, que les fichiers adaptent leur taille en fonction du volume de données, sans bloquer les utilisateurs.

Fichieràaccroissementmanuel

La croissance manuelle des fichiers permet de maîtriser le moment où le fichier va grossir et donc le moment où le
serveur va subir une charge de travail supplémentaire pour mettre en forme le fichier s’il s’agit d’un fichier de
données.

Ajout de fichiers

Pour permettre à une base de données d’obtenir plus de place, il est enfin possible de rajouter des fichiers. Cette
solution présente le double avantage de maîtriser l’instant où le serveur va subir une surcharge de travail, ainsi que
de ne pas fragmenter physiquement les fichiers surtout si ces derniers sont stockés sur une partition NTFS.
Cependant cette solution nécessite que l’administrateur observe de près l’utilisation des fichiers journaux et de
données afin de toujours intervenir avant que le système ne se bloque pour un manque d’espace disque.

Le journal des transactions

Le journal des transactions est composé de un à plusieurs journaux. Afin que le serveur fonctionne correctement, il
est indispensable que le journal ne soit jamais saturé. Le journal est vidé lors des sauvegardes et éventuellement à
chaque point de synchronisation si la base est configurée en mode de restauration simple.

Pour assurer une place suffisante au journal, la solution la plus facile consiste à positionner le ou les fichiers qui le
composent avec une croissance automatique.

Modifier un fichier en TransactSQL

C’est l’instruction ALTER DATABASE qui permet d’effectuer toutes les opérations relatives aux manipulations des
fichiers de base de données, aussi bien pour les fichiers de données que les fichiers du journal de transaction.

Toutes les caractéristiques des fichiers peuvent être modifiées, mais les modifications apportées doivent suivre
certaines règles comme, par exemple, la nouvelle taille du fichier qui doit être supérieure à la taille initiale.

ALTER DATABASE nomBaseDeDonnées

MODIFY FILE (spécificationFichier)[;]

Avec pour spécificationFichier les éléments de syntaxe suivants :

(NAME = nomLogique,
NEWNAME = nouveauNomlogique,
FILENAME = ’cheminEtNomFichier’
[,SIZE = taille [KB|MB|GB|TB]]
[,MAXSIZE={tailleMaximum[KB|MB|GB|TB]|UNLIMITED}]
[,FILEGROWTH = pasIncrement [KB|MB|GB|TB|%]]
)
Ajouter un fichier en TRANSACT SQL

C’est la commande ALTER DATABASE associée à l’option ADD FILE qui va permettre de rajouter un fichier de données
ou un fichier journal.

Syntaxe :

ALTER DATABASE nomBaseDeDonnées

ADD FILE spécificationFichier[;]

ModifieretajouterdesfichiersdepuisSQLServerManagement Studio
C’est par l’intermédiaire de la boîte de dialogue qui indique les propriétés de la base de données, qu’il est possible
de gérer les opérations sur la taille des fichiers et sur leur taille.
Les fichiers du journal
Comme pour les fichiers de données, il est possible de redimensionner le fichier journal ainsi que d’ajouter un fichier
au journal des transactions. Si la modification peut s’effectuer comme pour les fichiers de données par
l’intermédiaire de la commande ALTER DATABASE nomBaseDonnées MODIFY FILE…, l’ajout quant à lui nécessite
l’utilisation de l’option ADD LOG FILE. Bien entendu comme pour les fichiers de données, toutes ces opérations
peuvent être effectuées depuis SQL Server Management Studio.
b. Libérer de l’espace disque utilisé par des fichiers de données vides.

Lorsque les tables sont vidées de leurs données à l’aide des commandes DELETE ou TRUNCATE TABLE, les
extensions occupées par les tables et index sont alors libérées. Par contre, la taille des fichiers n’est pas réduite.
Pour réaliser une telle opération il est important de s’assurer que la totalité de l’espace libre est regroupée en fin
de fichier. Une fois cette opération réalisée, il est possible de tronquer le fichier sans jamais redescendre en
dessous de la taille initiale.

La mise en place de la réduction des fichiers se fait au moyen de deux commandes DBCC.

SHRINKDATABASE

Cette instruction permet de compacter l’ensemble des fichiers constituant la base de données (journaux et
données). Pour les fichiers de données toutes les extensions utilisées sont stockées de façon contiguë en haut du
fichier. Pour les fichiers journaux, cette opération de compactage intervient en différé et SQL Server essaie de
rendre aux fichiers journaux une taille aussi proche possible que celle de la taille cible lorsque les journaux sont
tronqués.

Syntaxe :

DBCC SHRINKDATABASE {nom_base_données|id_base_données|0}

[,pourcentage_cible]
[,{NOTRUNCATE|TRUNCATEONLY}])

nom_base_données

Nom de la base de données sur laquelle va porter l’exécution de la commande

id_base_données

Identifiant de la base de données. Cet identifiant peut être connu en exécutant la fonction db_id() ou bien en
interrogeant la vue sys.databases depuis la base master.

Permet de préciser que l’exécution portera sur la base courante.

pourcentage_cible

Permet de préciser en pourcentage l’espace libre souhaité dans le fichier après compactage.

NOTRUNCATE

Permet de ne pas rendre au système d’exploitation l’espace libre obtenu après compactage. Par défaut, l’espace
ainsi obtenu est libéré.

TRUNCATEONLY

Permet de libérer l’espace inutilisé dans les fichiers de données et compacte le fichier à la dernière extension
allouée. Aucune réorganisation physique des données n’est envisagée : déplacement des lignes de données afin de
compléter au mieux les extensions utilisées par l’objet. Dans un tel cas, le paramètre pourcentage_cible est ignoré.

SHRINKFILE

Cette instruction, semblable à DBCC SHRINKDATABASE permet de réaliser les opérations de compactage et
réduction de fichier de données par fichier.

Syntaxe :

DBCC SHRINKFILE ([nom_fichier|id_fichier]

{[[,taille_cible]
[,{NOTRUNCATE|TRUNCATEONLY}]]|EMPTYFILE}

taille_cible

Permet de préciser la taille finale souhaitée exprimée en méga octets sous forme de nombre entier. Si aucune taille
n’est spécifiée, la taille du fichier est réduite à son maximum.

EMPTYFILE

Cette commande permet de réaliser la migration de toutes les données contenues dans le fichier vers les autres
fichiers du même groupe. De plus, SQL Server n’utilise plus ce fichier. Il est alors possible de le supprimer de la base
de données à l’aide d’une commande ALTER DATABASE.

NOTRUNCATE

Permet de ne pas rendre au système d’exploitation l’espace libre obtenu après compactage. Par défaut, l’espace
ainsi obtenu est libéré.

TRUNCATEONLY

Permet de libérer l’espace inutilisé dans les fichiers de données et compacte le fichier à la dernière extension
allouée. Aucune réorganisation physique des données n’est envisagée : déplacement des lignes de données afin de
compléter au mieux les extensions utilisées par l’objet. Dans un tel cas le paramètre taille_cible est ignoré.

Exemple :
 La taille finale doit être supérieure à la taille de la base Model plus la taille des données.

Avant de réaliser une opération de compactage, il est prudent de réaliser une sauvegarde complète de la
base de données à compacter ainsi que de la base Master.

Les instructions DBCC SHRINKDATABASE et DBCC SHRINKFILE s’exécutent en mode différé, il est donc possible que
la taille des fichiers ne diminue pas de façon instantanée.

Seule l’instruction DBCC SHRINKFILE permet de réduire la taille d’un fichier à une taille inférieure à celle précisée lors
de la création du fichier. Evidemment, il n’est pas possible de descendre en dessous de la taille occupée par les
données.

c. Configuration de la base de données

Il est possible de paramétrer de nombreuses options au niveau de la base de données. Ce paramétrage est
possible soit avec l’instruction ALTER DATABASE en Transact SQL, soit depuis la fenêtre des propriétés de la base
dans SQL Server Management Studio. Tous les paramètres listés ci­dessous sont à fixer pour chaque base de
données. Il n’est donc pas possible de fixer des options sur plusieurs bases de données en une seule commande,
tandis qu’il est possible de préciser plusieurs options d’une base en une commande.
Si certains choix doivent être adoptés par toutes les bases utilisateur, il est préférable de changer les options de la
base Model, ainsi toute nouvelle base utilisateur, fonctionnera avec les paramètres définis dans Model.

ALTER DATABASE nomBaseDeDonnees

SET option [;]

Parmi toutes les options disponibles, il est possible d’en isoler quelques­unes :

Option Descriptif

AUTO_SHRINK {ON|OFF} Si cette option est activée les fichiers sont réduits dès qu’ils disposent de plus
de 25 % d’espace libre.

READ_ONLY Permet de positionner la base de données en mode lecture seule.

READ_WRITE La base de données est positionnée en mode lecture/ écriture.

SINGLE_USER Seul un utilisateur peut travailler sur la base de données.

RESTRICTED_USER Seuls les utilisateurs membres des rôles db_owner, db creator et sysadmin
peuvent se connecter à la base de données.

MULTI_USER C’est le mode de fonctionnement standard d’une base, en autorisant plusieurs

utilisateurs à travailler simultanément.

AUTO_CREATE_STATISTICS Lorsque cette option est positionnée à ON les statistiques manquantes lors de
{ ON | OFF } l’optimisation de la requête, sont calculées de façon automatique.

AUTO_UPDATE_STATISTICS Lorsque cette option est positionnée à ON, les statistiques obsolètes sont
{ ON | OFF} calculées de façon automatique

Les options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS ne concernent pas les tables système

ou bien à usage interne à SQL Server comme par exemple les index XML, les index de texte intégral, les
files d’attentes Service Broker. Pour toutes ces tables les statistiques sont créées et mises à jour quelle que soit
la valeur des options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS.

La fonction DATABASEPROPERTYEX permet de connaître la valeur actuelle de l’option qui lui est passée en
paramètre.

Pour régler les paramètres de la base il est possible de passer par :

SQLServerManagementStudio

Pour connaître et éventuellement modifier les paramètres d’une base de données, il faut afficher la fenêtre
présentant les propriétés de la base. Cette fenêtre est affichée en sélectionnant Propriétés depuis le menu
contextuel associé à la base de données.
 ALTERDATABASE

-
Options actuellement gérées
Avant de fixer un nouveau paramétrage de la base de données, il est important de connaître le paramétrage actuel.
La connaissance de ce paramétrage peut également aider à la compréhension du fonctionnement de la base. Il est
possible de lire les valeurs des différentes options depuis SQL Server Management Studio, mais la connaissance du
paramétrage de la base peut également être faite sous la forme de script Transact SQL.

Databasepropertyex

Pour connaître la valeur d’un paramètre.

sp_helpdb

Si aucun paramètre n’est passé, cette procédure permet de connaître l’ensemble des bases qui existent sur le
serveur. Les renseignements fournis sont le nom, la taille, le propriétaire, l’identificateur, la date de création et les
options.

Si un nom de base est passé en paramètre, la procédure permet de connaître les informations générales de la base
ainsi que les nom et emplacement des fichiers de données et des fichiers journaux.

La procédure sp_helpdb utilise la table sys.databases pour établir la liste des bases et les différentes options de
chacune d’elles.
sp_spaceused [nom_objet]

Permet de connaître l’espace de stockage utilisé par une base de données, un journal ou des objets de la base
(table...).

Il est possible au niveau de SQL Server Management Studio d’obtenir un rapport sur l’utilisation de l’espace disque
par les différentes tables de la base de données en cours. Pour cela, après s’être positionné sur la base de
données cible, il faut sélectionner l’option Rapports ­ Rapports standards. À ce niveau, trois choix sont disponibles
en fonction que l’on souhaite un rapport global sur l’espace disque utilisé, un rapport détaillé de la consommation
d’espace disque pour chaque table ou bien un rapport ne détaillant que les principales tables du serveur.
3. Supprimer une base de données

La suppression d’une base de données utilisateur est une opération ponctuelle qui peut être réalisée, comme la
plupart des opérations d’administration soit par SQL Server Management Studio, soit en Transact SQL. La
suppression de la base de données a pour conséquence de supprimer tous les fichiers qui correspondent à cette
base ainsi que toutes les données contenues dans cette base. Cette opération est irréversible et en cas de
mauvaise manipulation il est nécessaire de remonter une sauvegarde.

Après suppression d’une base de données, chaque connexion qui utilisait cette base par défaut se retrouve
sans base par défaut.

Afin de pouvoir réaliser d’éventuelles restaurations du serveur, il est important d’effectuer une sauvegarde
de la base Master après suppression d’une ou plusieurs bases utilisateur.

Les limites

Il n’est bien sûr pas toujours possible de supprimer une base, les principales limites sont :

● Lorsqu’elle est en cours de restauration,

● Lorsqu’elle est ouverte par un utilisateur, en lecture ou en écriture,

● Lorsqu’elle participe à une publication dans le cadre de la réplication.

Il n’est pas possible de supprimer les bases de données système.

a. Transact SQL

C’est par l’intermédiaire de la commande DROP DATABASE que seront supprimées les bases de données.

b. SQL Server Management Studio

Par un simple clic droit sur la base de données, vous avez accès au menu Supprimer dans le menu contextuel. Par
cette méthode, il n’est possible de supprimer les bases qu’une par une.