Trouver l’espace disque occupé par les fichiers, et l’espace libre dans ceux-ci

Publié le par

Voici quelques requêtes pour vous permettre d’auditer vos fichiers de base de données :

=> Par une requête

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
-------------------------------
-- Nicolas SOUQUET - 26/01/2010
-------------------------------
;WITH  
  CTE AS  
  (  
    SELECT name AS nomLogique,  
      physical_name AS nomPhysique,  
      CASE is_percent_growth
  WHEN 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(8)) + ' MB'
  ELSE CAST(growth AS VARCHAR(3)) + ' %'
      END AS accroissement,  
      CAST((size * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS tailleFichier_MB,  
      CAST((FILEPROPERTY(name, 'SpaceUsed') * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS espaceOccupe  
    FROM sys.database_files  
  )  
SELECT nomLogique,  
  nomPhysique,  
  tailleFichier_MB,  
  accroissement,  
  espaceOccupe AS espaceOccupe_MB,  
  tailleFichier_MB - espaceOccupe AS espaceLibre_MB,  
  CAST((CAST(espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%occupe],  
  CAST((CAST(tailleFichier_MB - espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%libre]  
FROM CTE

=> Avec l’instruction DBCC SHOWFILESTATS (non documentée):

Elle donne le nombre d’étendues (extents) occupées et le nombre total d’étendues allouées pour le fichier.
Une étendue étant constituée de 8 pages, et une page faisant 8192 octets, il vous faudra donc multiplier les nombres obtenus par 64Ko.

=> Avec la procédure stockée système sp_spaceused :

Elle retourne deux ensemble de résultats :
– l’espace occupé pour toute la base de données, mais attention seulement l’espace libre dans le fichier de données.
Рle nombre de Ko r̩serv̩s (= taille du fichier), utilis̩s (colonne data), d̩di̩s aux index et inutilis̩s.

=> Avec l’instruction DBCC SQLPERF(logspace)

Celle-ci ne procure que la taille totale du fichier du journal des transactions, ainsi que son pourcentage d’occupation.

Bonne gestion d’espace disque ;)

ElSuket.

=>=>=> 12/05/2010 : MAJ : un script pour connaître l’espace occupé par et dans les fichiers, pour toutes les bases de données d’une instance SQL Server :

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-------------------------------
-- 12/05/2010 - Nicolas SOUQUET
-------------------------------
SET NOCOUNT ON
GO
 
DECLARE @sql varchar(1024)
SET @sql = 'SELECT name AS file_logical_name,' + CHAR(13) + CHAR(10) + CHAR(9) +
  'physical_name AS file_physical_name,' + CHAR(13) + CHAR(10) + CHAR(9) +
  'CASE is_percent_growth' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
  'WHEN 0 THEN CAST(growth * 8 / 1024 AS varchar(8)) + '''' MB''''' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
  'ELSE CAST(growth AS varchar(3)) + '''' %''''' + CHAR(13) + CHAR(10) + CHAR(9) +
  'END AS file_growth,' + CHAR(13) + CHAR(10) + CHAR(9) +
  'CAST((size * 8) / CAST(1024 AS numeric(14, 2)) AS numeric(14, 2)) AS file_size_MB,' + CHAR(13) + CHAR(10) + CHAR(9) +
  'CAST((FILEPROPERTY(name, ''''SpaceUsed'''') * 8) / CAST(1024 AS numeric(14, 2)) AS numeric(14, 2)) AS file_used_space_MB' + CHAR(13) + CHAR(10) +
  'FROM sys.database_files''' + CHAR(13) + CHAR(10)
 
DECLARE @exec_sql varchar(1024)
 
DECLARE @instance_database_file_space TABLE
(
  database_name sysname
  , file_logical_name sysname
  , file_physical_name nvarchar(260)  
  , file_growth varchar(8)
  , file_size_MB numeric(14,2)
  , file_used_space_MB numeric(14,2)
  , file_free_space_MB numeric(14,2)
  , file_used_space_percent numeric(5,2)
  , file_free_space_percent numeric(5,2)
)
 
DECLARE @database_file_space TABLE
(
  file_logical_name sysname
  , file_physical_name nvarchar(260)  
  , file_growth varchar(8)
  , file_size_MB numeric(14,2)
  , file_used_space_MB numeric(14,2)
)
 
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT  name
  FROM  sys.databases
  WHERE  source_database_id IS NULL
FOR READ ONLY
 
DECLARE @database_name sysname
 
OPEN db_cur
FETCH NEXT FROM db_cur INTO @database_name
WHILE @@fetch_status = 0
BEGIN
  DELETE  @database_file_space
 
  SET @exec_sql = 'EXEC ' + @database_name + '.dbo.sp_executeSQL N''' + @sql + ''
 
  BEGIN TRY
    INSERT  @database_file_space
    EXEC  (@exec_sql)
  END TRY
  BEGIN CATCH
    PRINT @database_name + ' : ' + ERROR_MESSAGE()
  END CATCH
 
  INSERT  @instance_database_file_space
  SELECT  @database_name
    , file_logical_name
    , file_physical_name
    , file_growth
    , file_size_MB
    , file_used_space_MB
    , file_size_MB - file_used_space_MB AS file_free_space_MB
    , CAST((CAST(file_used_space_MB AS numeric(14, 2)) / file_size_MB) * 100 AS numeric(14, 2)) AS file_used_space_percent
    , CAST((CAST(file_size_MB - file_used_space_MB AS numeric(14, 2)) / file_size_MB) * 100 AS numeric(14, 2)) AS file_free_space_percent
  FROM  @database_file_space
 
  FETCH NEXT FROM db_cur INTO @database_name
END
CLOSE db_cur
DEALLOCATE db_cur
 
SELECT  *
FROM  @instance_database_file_space

Il arrive que la procédure stockée système sp_executeSQL ne soit pas trouvée dans la base de données.
Quand tel est le cas, la liste des bases de données est affichée dans la console de SSMS.

7 réflexions au sujet de « Trouver l’espace disque occupé par les fichiers, et l’espace libre dans ceux-ci »

  3. Bon … tu vois je lis et je teste ce que tu fais :-)

    Dis moi n’aurais tu pas oublié quelque chose pour l’accroissement ?

    …
    CAST(growth AS VARCHAR(8)) + CASE is_percent_growth
    WHEN 0 THEN ‘ MB’
    WHEN 1 THEN ‘ %’
    END AS accroissement,
    …

    En fait dans le cas d’un accroissement en MB, tu retournes le nombre de pages mais pas la taille en MB …

    Tu me diras tout ça dès que tu as un petit moment :-)

    ++

  5. …

    (size * 8) / 1024 AS tailleFichier,

    …

    Par exemple, si size = 100, size * 8 / 1024 = 800 / 1024 = 0 en division entière…

    Du coup, quand tu fais un  » / tailleFichier  » dans la suite de ton code, tu tombes sur le problème que mentionnais mikedavem !

  7. Je viens d’essayer .. bien j’en avais besoin.. Par contre si tu ne converties pas tes entiers en décimal, je pense que tu vas te retrouver avec des problèmes de division par zéro .. j’ai eu le cas pour la base master :-)

    …
    (size * 8.0) / 1024 AS tailleFichier,
    (FILEPROPERTY(name, ‘SpaceUsed’) * 8.0) / 1024 AS espaceOccupe

    …

Laisser un commentaire