una - Listado de información sobre todos los archivos de base de datos en SQL Server

tipos de archivos de datos

Question

¿Es posible enumerar información sobre los archivos (MDF / LDF) de todas las bases de datos en un servidor SQL?

Me gustaría obtener una lista que muestre qué base de datos está usando qué archivos en el disco local.

Lo que probé:

• exec sp_databases todas las bases de datos
• select * from sys.databases muestra mucha información sobre cada base de datos, pero desafortunadamente no muestra los archivos utilizados por cada base de datos.
• select * from sys.database_files muestra los archivos mdf / ldf de la base de datos master , pero no las otras bases de datos

Answer 1

Ejecutando el siguiente sql (solo funcionará cuando no tenga múltiples archivos mdf / ldf para la misma base de datos)

SELECT
    db.name AS DBName,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = ''ROWS'' and db.database_id = mf.database_id ) as DataFile,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = ''LOG'' and db.database_id = mf.database_id ) as LogFile
FROM sys.databases db

devolverá esta salida

DBName       DataFile                     LogFile
--------------------------------------------------------------------------------
master       C:/..../master.mdf           C:/..../mastlog.ldf
tempdb       C:/..../tempdb.mdf           C:/..../templog.ldf
model        C:/..../model.mdf            C:/..../modellog.ldf

y el resto de las bases de datos

Si sus TempDB tienen múltiples MDF (como el mío), este script fallará. Sin embargo, puedes usar

WHERE db.database_id > 4

al final y devolverá todas las bases de datos excepto las del sistema.

Answer 2

Este script enumera la mayoría de lo que está buscando y, con suerte, se puede modificar según sus necesidades. Tenga en cuenta que está creando una tabla permanente allí; es posible que desee cambiarla. Es un subconjunto de un script más grande que también resume la información de respaldo y trabajo en varios servidores.

IF OBJECT_ID(''tempdb..#DriveInfo'') IS NOT NULL
 DROP TABLE #DriveInfo
CREATE TABLE #DriveInfo
 (
    Drive CHAR(1)
    ,MBFree INT
 ) 

INSERT  INTO #DriveInfo
      EXEC master..xp_fixeddrives


IF OBJECT_ID(''[dbo].[Tmp_tblDatabaseInfo]'', ''U'') IS NOT NULL 
   DROP TABLE [dbo].[Tmp_tblDatabaseInfo]
CREATE TABLE [dbo].[Tmp_tblDatabaseInfo](
      [ServerName] [nvarchar](128) NULL
      ,[DBName] [nvarchar](128)  NULL
      ,[database_id] [int] NULL
      ,[create_date] datetime NULL
      ,[CompatibilityLevel] [int] NULL
      ,[collation_name] [nvarchar](128) NULL
      ,[state_desc] [nvarchar](60) NULL
      ,[recovery_model_desc] [nvarchar](60) NULL
      ,[DataFileLocations] [nvarchar](4000)
      ,[DataFilesMB] money null
      ,DataVolumeFreeSpaceMB INT NULL
      ,[LogFileLocations] [nvarchar](4000)
      ,[LogFilesMB] money null
      ,LogVolumeFreeSpaceMB INT NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Tmp_tblDatabaseInfo] 
SELECT 
      @@SERVERNAME AS [ServerName] 
      ,d.name AS DBName 
      ,d.database_id
      ,d.create_date
      ,d.compatibility_level  
      ,CAST(d.collation_name AS [nvarchar](128)) AS collation_name
      ,d.[state_desc]
      ,d.recovery_model_desc
      ,(select physical_name + '' | '' AS [text()]
         from sys.master_files m
         WHERE m.type = 0 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('''')) AS DataFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFilesMB
      ,NULL
      ,(select physical_name + '' | '' AS [text()]
         from sys.master_files m
         WHERE m.type = 1 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('''')) AS LogFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFilesMB
      ,NULL
FROM  sys.databases d  

WHERE d.database_id > 4 --Exclude basic system databases
UPDATE [dbo].[Tmp_tblDatabaseInfo] 
   SET DataFileLocations = 
      CASE WHEN LEN(DataFileLocations) > 4 THEN  LEFT(DataFileLocations,LEN(DataFileLocations)-2) ELSE NULL END
   ,LogFileLocations =
      CASE WHEN LEN(LogFileLocations) > 4 THEN  LEFT(LogFileLocations,LEN(LogFileLocations)-2) ELSE NULL END
   ,DataFilesMB = 
      CASE WHEN DataFilesMB > 0 THEN  DataFilesMB * 8 / 1024.0   ELSE NULL END
   ,LogFilesMB = 
      CASE WHEN LogFilesMB > 0 THEN  LogFilesMB * 8 / 1024.0  ELSE NULL END
   ,DataVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( DataFileLocations,1))
   ,LogVolumeFreeSpaceMB = 
      (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( LogFileLocations,1))

select * from [dbo].[Tmp_tblDatabaseInfo] 

Answer 3

Estoy usando script para obtener espacio libre en cada archivo:

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)   
Exec sp_msforeachdb ''
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''''SpaceUsed'''') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
''
Select * From ##temp
drop table ##temp

El tamaño se expresa en KB.

Answer 4

Puede usar sys.master_files .

Contiene una fila por archivo de una base de datos almacenada en la base de datos maestra. Esta es una vista única de todo el sistema.

Answer 5

Puedes usar lo siguiente:

SP_HELPDB [Master]
GO

Answer 6

Si desea obtener la ubicación de la base de datos, puede marcar Get All DBs Location .
puede usar sys.master_files para obtener la ubicación de db y sys.databse para obtener el nombre de db

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN 
    sys.databases db ON db.database_id = mf.database_id

Answer 7

También puedes probar esto.

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