Les commandes DBCC SHRINKFILE et SHRINKDATABASE peuvent ne pas fonctionnent en raison de peuplées colonnes text, ntext ou image

Symptômes

Les commandes DBCC SHRINKFILE et DBCC SHRINKDATABASE ne seront pas déplacer les données et réduire la taille du fichier si la valeur de EstimatedPages est égale à la valeur MinimumSize qui est signalée lorsque la commande est terminée. En raison de la modification des données des requêtes sur des tables avec des types de données d’objet binaire volumineux (BLOB) (texte, ntext, image), il est possible d’avoir plusieurs extensions uniformes allouées qui sont faiblement remplies avec des données (par exemple, seul 1 des 8 pages que les contrôles d’étendue est allouée), qui provoquent le DBCC SHRINKFILE et le DBCC SHRINKDATABASE commandes excessive d’estimer le nombre de EstimatedPages , et les commandes n’effectuent pas les opérations de réduction. En règle générale, dans les cas où ce comportement est connu, la taille d’une sauvegarde de base de données complète est jusqu'à 8 fois plus petite que la taille des fichiers de la base de données après l’exécution d’un DBCC SHRINKFILE sur les fichiers de base de données.

Résolution

Si les tables concernées n’ont pas à être continuellement en ligne pendant que vous corrigez cette situation, vous pouvez utiliser l’une des solutions suivantes pour résoudre ce problème :
  • Utilisez une instruction SELECT INTO pour transférer la table entière vers une nouvelle table dans la même base de données. Supprimer la table d’origine et puis exécutez une instruction DBCC SHRINKFILE. Renommez la nouvelle table le nom de la table d’origine.
  • Utilisez une instruction SELECT INTO pour transférer la table entière vers une nouvelle table dans une autre base de données. Tronquer la table d’origine et puis exécutez une instruction DBCC SHRINKFILE. Transférer les données à la table d’origine.
  • Pour copier la table mode natif, utilisez le programme de copie en bloc. Script de la table et puis supprimez la table existante. Exécutez une instruction DBCC SHRINKFILE. Créer une nouvelle table et utilisez le programme de copie en bloc pour copier les données à la table.
  • Utilisez les services de transformation de données (DTS, DATA Transformation Services) pour transférer la base de données entière à une nouvelle base de données. Services de Transformation de données SQL Server 7.0 ne peut pas transférer données text et image plus longues qui sont correctement à 64 Ko. Ce problème ne s’applique pas à la version de SQL Server 2000 des Services de Transformation de données.
    Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    257425 RÉSOLUTION : DTS objet transfert ne transfère pas les données BLOB supérieur à 64
    Vous pouvez rechercher des colonnes text et image qui sont supérieures à 64 Ko, la requête suivante :
    SELECT MAX (DATALENGTH (TextImageColumn)) FROM TableName
    Si les données de texte et l’image sont supérieures à 64 Ko, le transfert tronque les données et ne déclenche pas d’erreur, sauf dans les services DTS des fichiers journaux. Si vous avez des données text ou image est supérieures à 64 Ko, utilisez les informations dans le troisième élément de puce pour les tables contenant des données text ou image. Ensuite, utilisez DTS pour le reste des éléments dans la base de données.
Si la base de données doit être en ligne ou la taille de la base de données est trop grande pour déplacer les données dans un fichier externe ou une autre base de données, vous pouvez utiliser la commande DBCC SHRINKFILE avec l’option EMPTY_FILE. Pour utiliser cette solution de contournement, procédez comme suit :
  1. Si vous exécutez Microsoft SQL Server 7.0 Service Pack 3, activer les indicateurs de trace 1180 et 1197.
  2. Si vous exécutez Microsoft SQL Server 7.0 Service Pack 3 version 7.00.987 ou une version ultérieure, y compris Service Pack 4, activez l’indicateur de trace 1180.
  3. Si vous avez double à la base de données dans plusieurs fichiers pour des raisons de performances ou de problèmes d’espace disque, vous pouvez créer 1 ou plusieurs fichiers supplémentaires dans la base de données à l’aide de la commande ALTER DATABASE avec une taille totale comparable aux résultats de la requête suivante.
    select 'size in MB'=ceiling(sum(ei.pg_alloc)/128.000), sfg.groupnamefrom extentinfo ei inner join sysfiles sf on ei.[file_id] = sf.fileid and ei.[file_id] != 1
    inner join sysfilegroups sfg on sf.groupid = sfg.groupid
    group by sfg.groupname
    REMARQUE: la requête précédente utilise la table qui est créée par la requête dans la section « Informations supplémentaires » de cet article. En outre, les totaux de taille de fichier sont ventilées par groupe de fichiers.

  4. Exécuter une requête de DBCC SHRINKFILE, avec l’option EMPTY_FILE comme second paramètre, sur chaque fichier qui a rempli faiblement blob des extensions uniformes.
  5. Vous pouvez suivre la progression de la commande DBCC SRHINKFILE en créant une ou plusieurs tables avec le même schéma que la table extentinfo de la section « Informations supplémentaires » de cet article. Puis exécutez « INSERT... EXEC import_extentinfo« query pour remplir les données pour les nouvelles tables. Analyser les résultats, utilisez la requête suivante :
    select [file_id], 'page count'=sum(pg_alloc), 'min page'=min(page_id), 'max page'=max(page_id) from extentinfo group by [file_id]
    En outre, vous pouvez utiliser la requête suivante pour effectuer le suivi de la commande DBCC EXTENTINFO a été exécutée si les tables commencent par le même modèle de lettres et sont créées près de l’heure en tant que la commande DBCC EXTENTINFO :
    select crdate, [name] from sysobjects where type = 'U' and [name] like 'extent%' order by crdate
  6. Lorsque la requête de DBCC SHRINKFILE est terminée, exécutez une requête de ALTER DATABASE pour supprimer les anciens fichiers de la base de données.
  7. Vous pouvez éventuellement supprimer l’espace supplémentaire en exécutant une requête DBCC SHRINKFILE avec l’option TRUNCATEONLY sur les nouveaux fichiers qui ont été créés à l’étape 3.
  8. Si vous exécutez Microsoft SQL Server 7.0, désactiver les indicateurs de trace activée à l’étape 1 ou 2.

Plus d'informations

Vous pouvez exécuter la requête Transact-SQL suivante à partir d’un outil de requête tel que l’Analyseur de requête, si vous êtes connecté en tant qu’utilisateur disposant de droits d’administrateur système . Vous exécutez la requête Transact-SQL sur SQL Server pour déterminer si la commande DBCC SHRINKDATABASE et la commande ne peut pas réduire les fichiers car peu de DBCC SHRINKFILE rempli des extensions uniformes de BLOB.

REMARQUE: vous devez mettre à jour la chaîne '< nom de la base de données >' sur le nom de la base de données du problème et placez le nom entre guillemets simples.

use pubsgo
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8
begin
create table extentinfo
(
[file_id] smallint,
page_id int,
pg_alloc int,
ext_size tinyint,
obj_id int,
index_id tinyint,
pfs_bytes varbinary(10)
)
end
go
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7
begin
create table extentinfo
(
[file_id] smallint,
page_id int,
pg_alloc int,
ext_size tinyint,
obj_id int,
index_id tinyint,
pfs_bytes varbinary(10),
avg_used tinyint
)
end
go
create procedure import_extentinfo as
dbcc extentinfo('<database name>')
go
insert extentinfo exec import_extentinfo
go

select [file_id],obj_id, index_id, ext_size, 'actual extent count'=count(*), 'actual page count'=sum(pg_alloc),
'possible extent count'=ceiling(sum(pg_alloc)*1.0/ext_size),
'possible extents / actual extents' = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)
from extentinfo where ext_size != 1 and index_id = 255
group by [file_id],obj_id, index_id, ext_size
having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0
order by obj_id, index_id, [file_id]
Si vous voyez des lignes de résultat à partir de la dernière requête qui ont des valeurs de 255, index_id et autant que possible / réelle mesure colonne est inférieure à 75, cette base de données ont très peu peuplées étendues BLOB et taille peut être réduite par les méthodes décrites dans la section « Résolution » de cet article.

REMARQUE: cette requête s’appuie sur les fonctionnalités de la commande DBCC EXTENTINFO. N’utilisez pas la commande DBCC EXTENTINFO en dehors de cette situation car la fonctionnalité peut changer ou même être retirée des versions futures de Microsoft SQL Server.

Références

Pour plus d’informations, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft :
272220 RÉSOLUTION : espace de stockage de données TEXTE/IMAGE est ne pas réclamé correctement
308627 CORRECTIF : DBCC SHRINKDATABASE ou DBCC SHRINKFILE peut développer la base de données avec des données Text ou Image
Propriétés

ID d'article : 324432 - Dernière mise à jour : 9 janv. 2017 - Révision : 1

Commentaires