Affichage des informations sur tous les fichiers de base de données dans SQL Server

Question

Est-il possible de répertorier les informations sur les fichiers (MDF/LDF) de toutes les bases de données sur un serveur SQL ?

J'aimerais obtenir une liste montrant quelles bases de données utilisent quels fichiers sur le disque local.

Ce que j'ai essayé :

• exec sp_databases toutes les bases de données
• select * from sys.databases montre beaucoup d'informations sur chaque base de données - mais malheureusement cela ne montre pas les fichiers utilisés par chaque base de données.
• select * from sys.database_files montre les fichiers mdf/ldf de la base de données master - mais pas des autres bases de données

Answer 1

Vous pouvez également essayer ceci.

 select db_name(dbid) dbname, filename from sys.sysaltfiles

Answer 2

Le script ci-dessous peut être utilisé pour obtenir les informations suivantes : 1. Informations sur la taille de la base de données 2. Informations sur l'espace de fichier 3. AutoCroissance 4. Modèle de récupération 5. Informations sur la sauvegarde de réutilisation de log

CREATE TABLE #tempFileInformation
(
DBNAME          NVARCHAR(256),
[FILENAME]      NVARCHAR(256),
[TYPE]          NVARCHAR(120),
FILEGROUPNAME   NVARCHAR(120),
FILE_LOCATION   NVARCHAR(500),
FILESIZE_MB     DECIMAL(10,2),
USEDSPACE_MB    DECIMAL(10,2),
FREESPACE_MB    DECIMAL(10,2),
AUTOGROW_STATUS NVARCHAR(100)
)
GO

DECLARE @SQL VARCHAR(2000)

SELECT @SQL = '
 USE [?]
            INSERT INTO #tempFileInformation
            SELECT  
                DBNAME          =DB_NAME(),     
                [FILENAME]      =A.NAME,
                [TYPE]          = A.TYPE_DESC,
                FILEGROUPNAME   = fg.name,
                FILE_LOCATION   =a.PHYSICAL_NAME,
                FILESIZE_MB     = CONVERT(DECIMAL(10,2),A.SIZE/128.0),
                USEDSPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 - ((A.SIZE - CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT))/128.0))),
                FREESPACE_MB    = CONVERT(DECIMAL(10,2),(A.SIZE/128.0 -  CAST(FILEPROPERTY(A.NAME,''SPACEUSED'') AS INT)/128.0)),
                AUTOGROW_STATUS = ''BY '' +CASE is_percent_growth when 0 then cast (growth/128 as varchar(10))+ '' MB - ''
                                                                  when 1 then cast (growth as varchar(10)) + ''% - '' ELSE '''' END
                                                                  + CASE MAX_SIZE WHEN 0 THEN '' DISABLED '' 
                                                                                  WHEN -1 THEN '' UNRESTRICTED''
                                                                                  ELSE '' RESTRICTED TO '' + CAST(MAX_SIZE/(128*1024) AS VARCHAR(10)) + '' GB '' END
                                                                + CASE IS_PERCENT_GROWTH WHEn 1 then '' [autogrowth by percent]'' else '''' end
    from sys.database_files A
    left join sys.filegroups fg on a.data_space_id = fg.data_space_id
    order by A.type desc,A.name
    ;
    '

    --print @sql

    EXEC sp_MSforeachdb @SQL
    go

    SELECT dbSize.*,fg.*,d.log_reuse_wait_desc,d.recovery_model_desc
    FROM #tempFileInformation fg
    LEFT JOIN sys.databases d on fg.DBNAME = d.name
    CROSS APPLY
    (
        select dbname,
                sum(FILESIZE_MB) as [totalDBSize_MB],
                sum(FREESPACE_MB) as [DB_Free_Space_Size_MB],
                sum(USEDSPACE_MB) as [DB_Used_Space_Size_MB]
            from #tempFileInformation
            where  dbname = fg.dbname
            group by dbname
    )dbSize

go
DROP TABLE #tempFileInformation

Answer 3

En utilisant ce script, vous pouvez afficher le nom de toutes les bases de données et les fichiers utilisés (à l'exception des bases de données système).

sélectionnez nom, physical_name de sys.master_files où database_id > 4

Answer 4

Pour contourner les requêtes qui génèrent une erreur lorsqu'il existe plusieurs fichiers de données (par exemple les types de fichiers ".ndf"), essayez cette version, elle remplace les sous-requêtes par des jointures.

Voici une version de votre requête utilisant des jointures au lieu des sous-requêtes.

Santé!

SELECT
    db.name AS NomBD,
    db.database_id,
    mfr.physical_name AS FichierDonnées,
    mfl.physical_name AS FichierJournal
FROM sys.databases db
    JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='RANGÉES'
    JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='JOURNAL'
ORDER BY db.database_id

Résultats Exemple: (Veuillez noter que le fichier journal unique est associé à chaque MDF et NDF pour une seule base de données)

Answer 5

Vous pouvez également utiliser cette requête SQL pour récupérer la liste des fichiers :

SELECT d.name AS NomBaseDonnees, 
       m.name AS NomLogique, 
       m.physical_name AS NomPhysique, 
       taille AS TailleFichier
FROM sys.master_files m
     INNER JOIN sys.databases d ON(m.database_id = d.database_id)
     where d.name = ''
ORDER BY physical_name ;