"Primary Filegroup is Full" dans SQL Server 2008 Standard sans raison apparente

Question

Notre base de données est actuellement à 64 Go et l'une de nos applications a commencé à échouer avec l'erreur suivante :

System.Data.SqlClient.SqlException : Impossible d'allouer de l'espace pour l'objet 'cnv.LoggedUnpreparedSpos'.'PK_LoggedUnpreparedSpos' dans la base de données 'travelgateway' parce que le 'PRIMARY' Le groupe de fichiers est plein. Créez de l'espace disque en supprimant les fichiers inutiles, en déposant des objets dans le groupe de fichiers, en ajoutant des fichiers supplémentaires au groupe de fichiers ou en activant la croissance automatique pour les fichiers existants dans le groupe de fichiers.

J'ai tout vérifié : tous les fichiers d'un même groupe de fichiers sont autorisés à croître automatiquement avec des incréments raisonnables (100 Mo pour un fichier de données, 10 % pour un fichier journal), plus de 100 Go d'espace libre sont disponibles pour la base de données, tempdb est également configuré pour une croissance automatique avec beaucoup d'espace libre sur son disque dur.

Pour résoudre un problème, j'ai ajouté un deuxième fichier au groupe de fichiers et l'erreur a disparu. Mais je me sens mal à l'aise dans cette situation.

Où est le problème, les gars ?

Answer 1

OK, ça marche. Il s'est avéré qu'un volume NTFS où se trouvaient les fichiers de la base de données a eu lourdement fragmenté. J'ai arrêté le serveur SQL, défragmenté le tout et tout s'est bien passé depuis.

Answer 2

Anton,

La meilleure pratique consiste à ne pas créer d'objets utilisateur dans le groupe de fichiers principal. Lorsque vous avez de la bande passante, créez un nouveau groupe de fichiers et déplacez les objets utilisateur et laissez les objets système dans le groupe primaire.

Les requêtes suivantes vous aideront à identifier l'espace utilisé dans chaque fichier et les tables qui ont le plus grand nombre de lignes et s'il y a des tas. C'est un bon point de départ pour étudier ce problème.

SELECT  
ds.name as filegroupname
, df.name AS 'FileName' 
, physical_name AS 'PhysicalName'
, size/128 AS 'TotalSizeinMB'
, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB' 
, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds  
    ON df.data_space_id = ds.data_space_id;

EXEC xp_fixeddrives
select  t.name as TableName,  
    i.name as IndexName, 
    p.rows as Rows
from sys.filegroups fg (nolock) join sys.database_files df (nolock)
    on fg.data_space_id = df.data_space_id join sys.indexes i (nolock) 
    on df.data_space_id = i.data_space_id join sys.tables t (nolock)
    on i.object_id = t.object_id join sys.partitions p (nolock)
on t.object_id = p.object_id and i.index_id = p.index_id  
where fg.name = 'PRIMARY' and t.type = 'U'  
order by rows desc
select  t.name as TableName,  
    i.name as IndexName, 
    p.rows as Rows
from sys.filegroups fg (nolock) join sys.database_files df (nolock)
    on fg.data_space_id = df.data_space_id join sys.indexes i (nolock) 
    on df.data_space_id = i.data_space_id join sys.tables t (nolock)
    on i.object_id = t.object_id join sys.partitions p (nolock)
on t.object_id = p.object_id and i.index_id = p.index_id  
where fg.name = 'PRIMARY' and t.type = 'U' and i.index_id = 0 
order by rows desc

Answer 3

J'ai également rencontré le même problème, où la taille initiale de la base de données est fixée à 4 Go et la croissance automatique est fixée à 1 Mo. Le disque virtuel crypté TrueCrypt sur lequel se trouvait la base de données semblait disposer de beaucoup d'espace.

J'ai changé deux ou trois choses (ci-dessus) :

• J'ai désactivé le service Windows pour Sql Server Express de automatique à manuel , donc seul le serveur Sql 'normal' fonctionne. (Même si j'utilise Sql Server 2008 R2 qui devrait permettre 10 Go).
• J'ai changé la croissance automatique de 1 MB à 10%.
• J'ai modifié la taille de l'incrément de croissance automatique de 10 % à 1000 Mo.
• J'ai défragmenté le disque
• J'ai rétréci la base de données :
  • manuellement DBCC SHRINKDATABASE('...')
  • automatiquement un clic droit sur la base de données | "propriétés" | "Auto Shrink" | "Tronquer le journal au point de contrôle")

Tout cela sans grand résultat (j'ai pu insérer quelques enregistrements supplémentaires, mais j'ai vite rencontré le même problème). Le pagefile mentionné par Tobbi, m'a fait essayer un lecteur virtuel plus grand. (Même si mon lecteur ne devrait pas contenir de tels fichiers système, puisque je fonctionne sans qu'il soit monté la plupart du temps).

• J'ai créé un nouveau disque virtuel plus grand avec TrueCrypt.

Lors de la réalisation de ce projet, je me suis heurté à une question concernant TrueCrypt : si je dois stocker des fichiers de plus de 4 Go ( comme indiqué dans cette question SuperUser ).

• J'ai dit à TrueCrypt que j'allais stocker des fichiers de plus de 4 Go.

Après ces deux derniers, je me sentais bien, et je suppose que ce dernier a fait l'affaire. Je pense que TrueCrypt choisit un exfat système de fichiers ( comme décrit ici ), qui limite tous les fichiers à 4 Go. (Je n'avais donc probablement pas besoin d'agrandir le disque après tout, mais je l'ai fait quand même).

Il s'agit probablement d'un cas limite très rare, mais peut-être que cela peut aider quelqu'un.

Answer 4

Je viens de rencontrer le même problème. La raison en était que le fichier de mémoire virtuelle "pagefile.sys" était situé sur le même disque que les fichiers de données de nos bases de données (disque D :). Il avait doublé de taille et remplissait le disque mais Windows ne le détectait pas, c'est-à-dire qu'il semblait avoir 80 Go de libre alors que ce n'était pas le cas.

Le redémarrage du serveur SQL n'a pas aidé, peut-être que la défragmentation aurait donné au système d'exploitation le temps de libérer la pagefile, mais nous avons juste redémarré le serveur et voilà, la pagefile avait rétréci et tout fonctionnait bien.

Ce qui est intéressant, c'est que pendant les 30 minutes où nous avons enquêté, Windows n'a pas du tout calculé la taille du pagefile.sys (80 Go). Après le redémarrage, Windows a trouvé le pagefile et a inclus sa taille dans l'utilisation totale du disque (maintenant 40 Go - ce qui est encore trop grand).

Answer 5

J'ai découvert que cela arrive parce que : http://support.microsoft.com/kb/913399

SQL Server ne libère toutes les pages qu'une table de tas utilise que lorsque les conditions suivantes sont vraies : Une suppression sur cette table se produit. A verrou de niveau table est maintenu. Remarque Une table de type tas est une table qui n'est pas associée à un index clusterisé.

Si les pages ne sont pas désallouées, les autres objets de la base de données ne peuvent pas réutiliser les pages. réutiliser ces pages.

Toutefois, lorsque vous activez un niveau d'isolement basé sur le versionnement des lignes dans une base de données SQL Server 2005, les pages ne peuvent pas être libérées même si un verrou de niveau verrou de niveau table est maintenu.

La solution de Microsoft : http://support.microsoft.com/kb/913399

Pour contourner ce problème, utilisez l'une des méthodes suivantes : Inclure une indication TABLOCK dans l'instruction DELETE si un niveau d'isolation basé sur la version de la ligne n'est pas activé. si un niveau d'isolement basé sur le versionnement des rangées n'est pas activé. Par exemple, utilisez une instruction qui ressemble à l'instruction suivante :

DELETE de NomTable avec (TABLOCK)

Note représente le nom de la table. Utilisez la commande TRUNCATE TABLE si vous souhaitez supprimer tous les enregistrements de la table. Par exemple, utilisez une instruction similaire à la suivante :

TRUNCATE TABLE TableName

Créer un index clusterisé sur une colonne de la table. Pour plus d'informations informations sur la création d'un index clusterisé sur une table, consultez la section rubrique "Création d'un index clusterisé" dans SQL

Vous remarquerez au bas du lien qu'il n'est PAS indiqué que cela s'applique à SQL Server 2008, mais je pense que c'est le cas.