Listado de información sobre todos los archivos de base de datos en SQL Server
¿Es posible listar 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 intenté:
-
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 datosmaster
- pero no los otros bases de datos
7 answers
Puede usar sys.master_files .
Contiene una fila por archivo de una base de datos como se almacena en el maestro base. Esta es una visión única de todo el sistema.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-03-09 07:28:14
Si desea obtener la ubicación de la base de datos, puede verificar Obtener toda la ubicación de DBs.
puede usar sys.master_files
para obtener la ubicación de la bd y sys.databse
para obtener el nombre de la bd
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:34:34
Estoy usando script para obtener espacio vacío 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-09-11 07:55:45
También puedes probar esto.
select db_name(dbid) dbname, filename from sys.sysaltfiles
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-02-26 01:11:40
Ejecutando el siguiente sql (solo funcionará cuando no tenga varios 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 los míos), este script fallará. Sin embargo, puede utilizar
WHERE db.database_id > 4
Al final y devolverá todas las bases de datos excepto las bases de datos del sistema.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-20 15:16:02
Este script enumera la mayor parte de lo que está buscando y 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 copia de seguridad y la información del 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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-01 04:23:35
Puedes usar lo siguiente:
SP_HELPDB [Master]
GO
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-07-08 11:30:29