Discussion :

[Arnard]

Bonjour,

j'ai un serveur SQL Server 2005 SP3, avec une table d'exploitation et X tables d'historiques.

Je souhaite nettoyer une des tables d'historiques. Pour cela j'ai procédé à un truncate sur chacune des tables, ce qui doit libérer l'espace disque de la table, qui a bien fonctionné, et une commande SHRINKDATABASE sur la base, pour libérer l'espace disque du journal, commande qui m'a renvoyé ce message d'erreur :

DBCC SHRINKDATABASE : le fichier ID 1 de la base de données ID 7 a été ignoré, parce qu'il n'a pas assez d'espace disque disponible à récupérer.

Impossible de compacter le fichier journal 2 (2009.log) car tous les fichiers journaux logiques sont utilisés.

J'avais déjà pu faire cette opération auparavant sans souci sur une autre table de même type, pourquoi pas celle-là ? Que dois-je faire alors ?

Remarque : mon journal pèse quelques gigas, on n'a apparemment pas la même notion d'espace disque récupérable

Merci

[elsuket]

Bonjour,

SQL Server ne restitue jamais l'espace disque qu'il s'est échiné à obtenir, même si vous faites un TRUNCATE ou un DELETE.
Dans le cas d'un TRUNCATE, les pages sont vidées de leurs données mais pas supprimées du fichier de données qui supporte la table : elles peuvent resservir pour une insertion de données future.

une commande SHRINKDATABASE sur la base, pour libérer l'espace disque du journal

Avant de réduire le fichier du journal des transactions, vous devez le tronquer :

- si vous êtes en mode de récupération SIMPLE, le problème ne se pose pas car le fichier du journal des transactions est tronqué après chaque point de contrôle, toutes les minutes par défaut,

- si vous êtes en mode de récupération FULL, en effectuant soit une sauvegarde complète de la base de données, soit en effectuant une sauvegarde du fichier du journal des transactions à l'aide de l'instruction BACKUP LOG.

On peut en déduire que vous êtes mon mode complet et que vous n'avez jamais fait de sauvegarde de votre fichier du journal des transactions, ou peut-être même de la base de données elle-même.

Pour le savoir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'maBD'

Si vous n'avez pas un besoin élevé de récupération de données (il n'est par exemple pas acceptable de perdre 5 minutes de données), laissez votre base de données en mode FULL et effectuez des sauvegardes de votre fichier du journal des transactions à la même fréquence que la quantité de données que vous pouvez vous permettre de perdre.

Si en revanche vous pouvez vous permettre de perdre par exemple une journée de travail, effectuez une sauvegarde complète de votre base de données tous les jours, après être passé dans le mode de restauration SIMPLE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER DATABASE maBD
SET RECOVERY SIMPLE

Vous pourrez ensuite réduire votre fichier du journal des transactions, sauf s'il reste une transaction ouverte.
Pour le savoir : DBCC OPENTRAN.

Essayez de réserver les réductions de journaux aux cas d'urgence, c'est-à-dire le manque d'espace disque

@++

[mikedavem]

Si vous voulez juste réduire votre journal utilisez plutôt la commande DBCC SHRINKFILE. Comme vous le dit elsuket il faudra d'abord effectuer des sauvegardes du journal avant de pouvoir le réduire.

++

[Arnard]

Hello, et merci pour ces retours.

Ma procédure se veut automatique, pouvant cibler une base X. Hors un shrink file impose de connaitre le chemin des fichiers de chaque base, alors que shrink database me permet de cibler la base direct...

Sinon oui la procédure est prévue pour tourner après une sauvegarde, mais cette sauvegarde est réalisée par un outil tiers HP. Donc je ne maitrise pas les accès Alter Table !

Et il n'y aura pas d'insertions de futures données, sauf cas exceptionnel de restauration, donc sql server n'a pas à me piquer mon espace disque

[elsuket]

il n'y aura pas d'insertions de futures données [...] donc sql server n'a pas à me piquer mon espace disque

Et bien si ! : comme je vous l'ai montré, toute base de données SQL Server comporte au moins deux fichiers : le fichier de données, et le fichier du journal des transactions.
Supposons que vous n'effectuiez plus sur cette base de données que des instructions de modification de données UPDATE et DELETE.
Si vous n'effectuez pas régulièrement les sauvegardes comme je vous l'ai indiqué, certes votre fichier de données n'évoluera pas en taille (il grossira peut être un peu si vous mettez à jour une colonne de type chaîne de caractères dont la longueur serait plus élevée que les chaînes qui y étaient précédemment stockées), mais le fichier du journal des transaction, lui, peut continuer de voir sa taille s'élever, puisque celui-ci reflète la vie de la base de données : il permet d'en garantir l'intégrité en cas de crash, et de revenir à un point dans le temps, par exemple quelques secondes avant l'occurrence du problème.

un shrink file impose de connaitre le chemin des fichiers de chaque base, alors que shrink database me permet de cibler la base direct...

Si c'est plus rapide, c'est aussi plus absurde : faire les choses correctement a toujours pris du temps.

SQL Server 2005 a introduit une grand nombre de vues système qui vous permettent de trouver tout cela, il suffit de fouiller un peu

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT D.name AS nomBD,
		MF.name AS nomLogiqueFichierJT,
		MF.physical_name AS nomPhysiqueFichierJT
FROM sys.master_files AS MF
JOIN sys.databases AS D
	ON MF.database_id = D.database_id
	AND MF.type_desc = 'LOG'
WHERE D.name = 'maBD'

Vous avez avec cette requête tout ce dont vous avez besoin pour réduire un fichier de toutes ou d'une base de données seulement.

Mais je vous le répète : réservez la manœuvre de réduction de taille de fichiers aux cas d'urgence, c'est-à-dire le manque d'espace disque.

Si vous avez des problèmes d'espace disque, il est temps d'en acheter, et cela montre aussi que la base de données que vous voulez maintenir a été mal dimensionnée au départ.

@++