réduire la taille d'une DB

Version imprimable

09/07/2010, 00h16
Paul Van Walleghem

réduire la taille d'une DB

Bonjour à tous!

Sous Windows 7, je travaille avec Visual Studio 2010 Premium et développe une application sous Visual Basic.

Je souhaite réduire la taille d'une DB créée avec SQL SERVER 2008 Express (pas la R2).

Comment dois-je faire? Puis-je écrire une procédure stockée pour cela? Et, ensuite, l'appeler à partir du code VB ?

Merci de l'info!
Avec mes sincères salutations,
09/07/2010, 07h16
elsuket
Bonjour,

Il faut d'abord déterminer comment et pourquoi votre base de données a grossi.

Commencez par utiliser la première requête de ce billet pour savoir quel fichier(s) occupent le plus de place.

Quel est le mode de restauration de votre base de données ?
Code:

1 2 3 SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'maBD'
Si vous êtes en FULL, alors toutes les transactions sont enregistrées dans le fichier du journal des transactions, et si vous ne faites pas régulièrement une sauvegarde complète ou du fichier du journal des transactions, celui va grossir indéfiniment jusqu'à prendre toute la place disponible sur le disque dur.
Le mode FULL vous permet de ne perdre aucune modification de données ou de structure, mais il nécessite la mise en place de sauvegardes régulières pour permette la réutilisation de l'espace alloué au fichier du journal des transactions.
Cela conduit donc, au bout d'un certain temps, à avoir une taille de ce fichier qui demeure stable.

Si vous êtes en mode SIMPLE, le fichier du journal des transactions ne conserve que les transactions en cours d'exécution, puis réutilise directement l'espace alloué au fichier du journal.
Donc vous pouvez très bien vous permettre de passer en mode SIMPLE si ce n'est pas le cas, mais sachez que dans ce cas-là toutes les modifications de données ou de structure que vous aurez faites depuis la dernière sauvegarde complète de la base de données peuvent être perdues.

Enfin si vous faites beaucoup de chargement de données (intégration de fichiers, ...) vous avez peut-être intérêt à passer au mode de récupération BULK_LOGGED.
Celui-ci journalise de façon minimale ce type de transactions.
Mais cela ne vous dispensera pas d'effectuer des sauvegardes régulières de votre fichier du journal des transactions pour voir son espace réutilisé.

Finalement, dans le cas où votre fichier du journal des transactions est correctement géré, sachez que réduire la taille d'une base de données doit être réservé aux stricts cas d'urgence (manque d'espace disque), et que dans tous les cas l'espace que vous chipez à la base de données sera tout ou tard ré-alloué par le moteur de base de données.
Cela conduit donc à la fragmentation du fichier du journal des transactions, qui, comme vous vous en doutez, est assez contre-performante.
Vous avez donc tout intérêt à tailler ce fichier dès la création de la base de données.
On conseille en général 30% de la taille du fichier de données.

Sachant que le temps d'écriture sur disque est à peu près 1000 fois plus lent que dans la RAM, si une transaction attend la fin de l'allocation d'espace pour le fichier du journal des transactions, celle-ci peut durer longtemps :)

@++ ;)
09/07/2010, 08h21
Bluedeep

Citation:

Envoyé par Paul Van Walleghem

Je souhaite réduire la taille d'une DB créée avec SQL SERVER 2008 Express (pas la R2).

Voir les instructions DROP et DELETE :D

Désolé, j'ai pas pu m'en empêcher :oops:
09/07/2010, 09h03
Ptit_Dje

Citation:

Envoyé par Bluedeep

Voir les instructions DROP et DELETE :D

Désolé, j'ai pas pu m'en empêcher :oops:

DROP et DELETE sont des operations qui ne reduiront pas la taille de la DB. L'espace de stockage precedemment alloue sera reutilise, pas rendu au systeme.
09/07/2010, 11h25
jimb2k

Bonjour voici des liens que je trouve intéressent et peuvent t'aider à propos ta demande :

http://sqlpro.developpez.com/cours/sqlserver/log/

http://technet.microsoft.com/fr-fr/l.../ms189035.aspx

http://technet.microsoft.com/fr-fr/l.../ms189035.aspx

http://msdn.microsoft.com/fr-fr/library/ms189080.aspx

http://www.pckult.net/tutoriaux/sql/...de-donnees-sql

http://www.hilpers.fr/919543-possibi...uire-la-taille

un conseil : Cherche bien avant de poster ta demande SVP :ccool:

Bien à vous

Cdt,
09/07/2010, 16h26
Paul Van Walleghem

Bonjour !
D’abord, merci de vos réponses si détaillée.
Primo, la base de données a été mal sauvegardée dès le départ. Elle vient d’un PC où le SQL Management s’est planté et où il n’a jamais pu être réinstallé correctement. J’ai tout de même copié les fichiers *.mdf et *.ldf. Par chance, je peux me connecter à la DB sous Visual Studio.
Secundo, réduire la taille ? En fait, malgré que je n’ai plus Sql Server Express, la taille de ma DB semble encore être limitée à 4 GB. Pourriez-vous me dire si c’est possible ? Ou, est-ce que je me trompe ?
De manière intuitive, je sais quelle est la table de la DB qui prend le plus de place. C’est celle où je stocke beaucoup de BitMaps.
Comme je n’ai jamais programmé en SQL proprement dit, je crains surtout d’écrire des instructions là où il ne faut pas et de faire des bêtises. Dans une procédure stockée, toutes les instructions SQL sont-elles admises et autorisées ? Ou il y a-t-il une autre sorte de procédures pour des commandes comme SHRINKDATABASE ?
Avec mes sincères salutations,
09/07/2010, 16h44
Paul Van Walleghem

Merci encore de vos conseils!:ccool: Désolé du dérangement. Mais comme j'ai peu d'expérience avec sql server et que j'ai changé de version, je me sentais un peu perdu.
09/07/2010, 17h01
jimb2k

Bonjour par rapport ta demande

je crois si tu connais bien la table et la colonne ou quelle tu conserve des Bitmaps y'a une solution sous VB.Net

c'est de convertir l'image en une chaine de Bit

ça reduit bcp la taille de l'enregistrement et aide à ne pas atteindre les limites de ta base sous SQL Server Express

Bien à vous

Cdt,
09/07/2010, 17h20
Paul Van Walleghem

Merci beaucoup du conseil:ccool:

Citation:

En fait, malgré que je n’ai plus Sql Server Express, la taille de ma DB semble encore être limitée à 4 GB. Pourriez-vous me dire si c’est possible ? Ou, est-ce que je me trompe ?

Effectivement la version 2005 de l'édition Express de SQL Server 2005 est limitée à 4GB.
Ce n'est plus le cas avec la même édition de SQL Server 2008, où la limite a été repoussée à 10GB. Peut-être faut-il envisager d'y passer ;)

Citation:

De manière intuitive, je sais quelle est la table de la DB qui prend le plus de place. C’est celle où je stocke beaucoup de BitMaps.

Essayez le script suivant pour en être certain :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 SET NOCOUNT ON; DECLARE @TbTableSize TABLE ( name NVARCHAR(128), rows CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ); DECLARE curTables CURSOR FOR SELECT name FROM sys.tables WHERE schema_id = SCHEMA_ID('dbo') FOR READ ONLY; DECLARE @szNomTable SYSNAME; OPEN curTables; FETCH NEXT FROM curTables INTO @szNomTable WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @TbTableSize EXEC sp_spaceused @szNomTable; FETCH NEXT FROM curTables INTO @szNomTable END; DEALLOCATE curTables; UPDATE @TbTableSize SET reserved = REPLACE(reserved, ' KB', ''), data = REPLACE(data, ' KB', ''), index_size = REPLACE(index_size, ' KB', ''), unused = REPLACE(unused, ' KB', ''); SELECT name, rows, CAST(reserved AS INT) [Reservé (Ko)], CAST(data AS INT) AS [Données (Ko)], CAST(index_size AS INT) [Index (Ko)], CAST(unused AS INT) [Libre (Ko)], COALESCE(NULLIF(rows, 0), 1) / COALESCE(NULLIF(data, 0), 1) [Cout absolu ligne (Ko)], COALESCE(NULLIF(rows, 0), 1) / ((COALESCE(NULLIF(data, 0), 1) + COALESCE(NULLIF(reserved, 0), 1) + COALESCE(NULLIF(index_size, 0), 1))) [Cout relatif ligne (Ko)] FROM @TbTableSize ORDER BY CAST(rows AS INT) DESC
Citation:

Dans une procédure stockée, toutes les instructions SQL sont-elles admises et autorisées ? Ou il y a-t-il une autre sorte de procédures pour des commandes comme SHRINKDATABASE ?

Vous pouvez utiliser toute instruction T-SQL qui se trouve dans la documentation de SQL Server.
C'est-à-dire que vous pouvez tout-à-fait écrire une procédure stockée de maintenance de votre base de données avec sauvegarde, réduction, défragmentation des index, ...

@++ ;)

13/07/2010, 16h10
Paul Van Walleghem

Citation:

Envoyé par elsuket

Effectivement la version 2005 de l'édition Express de SQL Server 2005 est limitée à 4GB.
Ce n'est plus le cas avec la même édition de SQL Server 2008, où la limite a été repoussée à 10GB. Peut-être faut-il envisager d'y passer ;)

Permettez-moi, mais ça dépend des versions Express de SQL server 2008, il me semble. Vous avez raison pour la version R2. Mais, avant elle, Microsoft a sorti une version 2008 toujours limitée à 4GB.

Pour un novice (comme moi), il devient vraiment compliqué de se retrouver dans toutes ces versions. Surtout qu'au premier abord, rien ne les distingue… A ce propos, peut-on entièrement se fier aux paramètres de la chaîne de connexion pour voir si la DB appartient à une édition de SQL Express, ou pas ? Il y a-t-il une meilleure technique pour s’assurer de cela ?

L’installation de SQL Management Studio se plante et je n’arrive pas à configurer l’augmentation de la taille du journal des transactions correctement. Je vais donc migrer de l’édition Express à la version Pro de Sql Server 2008. Je suppose bien que c’est une opération assez délicate, mais j’espère que j’aurai moins de soucis par après…
Avec mes sincères salutations,
14/07/2010, 05h01
elsuket

Citation:

Permettez-moi, mais ça dépend des versions Express de SQL server 2008, il me semble. Vous avez raison pour la version R2. Mais, avant elle, Microsoft a sorti une version 2008 toujours limitée à 4GB.

Vous avez raison :)

Citation:

Pour un novice (comme moi), il devient vraiment compliqué de se retrouver dans toutes ces versions. Surtout qu'au premier abord, rien ne les distingue…

Vous retrouverez je pense à travers toutes les versions, l'édition :
- Express (gratuite mais atrophiée)
- Enterprise (toutes les fonctionnalités)
- Standard (pareil qu'Enterprise, mais sans quelques fonctionnalités avancées)
- Developer (pareil qu'Enterprise, mais ne requiert pas un configuration physique de serveur)

Vous pouvez jeter un oeil ici pour les autres éditions.

Citation:

A ce propos, peut-on entièrement se fier aux paramètres de la chaîne de connexion pour voir si la DB appartient à une édition de SQL Express, ou pas ?

Non cas vous pouvez nommer une instance SQL Server avec l'édition Express.
Pour connaître la version, l'édition et le service pack d'une version, vous pouvez utiliser la requête que j'ai publié ici

Citation:

L’installation de SQL Management Studio se plante

Quel est le libellé de l'erreur ?

Citation:

je n’arrive pas à configurer l’augmentation de la taille du journal des transactions correctement

C'est à dire ?

Citation:

Je vais donc migrer de l’édition Express à la version Pro de Sql Server 2008

Vous parlez de l'édition Developer ?
Si c'est le cas je pense que c'est un bon choix pour vous si vos besoins en bases de données augmentent ...

Citation:

Je suppose bien que c’est une opération assez délicate, mais j’espère que j’aurai moins de soucis par après…

Procédez simplement à une sauvegarde complète de votre base de données avant d'installer l'édition Developer.
Vous n'aurez alors qu'à restaurer celle-ci, donc ce n'est pas "gore" ;)

@++ ;)