Is it possible to list information about the files (MDF/LDF) of all databases on an SQL Server?
I'd like to get a list showing which database is using what files on the local disk.
What I tried:
exec sp_databases all databasesselect * from sys.databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database.select * from sys.database_files shows the mdf/ldf files of the master database - but not the other databases
You can use sys.master_files.
Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
If you want get location of Database you can check Get All DBs Location.
you can use sys.master_files for get location of db and sys.database to get db name
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
I am using script to get empty space in each file:
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
Size is expressed in KB.
I've created this query:
SELECT
db.name AS [Database Name],
mf.name AS [Logical Name],
mf.type_desc AS [File Type],
mf.physical_name AS [Path],
CAST(
(mf.Size * 8
) / 1024.0 AS DECIMAL(18, 1)) AS [Initial Size (MB)],
'By '+IIF(
mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
(mf.growth * 8
) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth],
IIF(mf.max_size = 0, 'No growth is allowed', IIF(mf.max_size = -1, 'Unlimited', CAST(
(
CAST(mf.max_size AS BIGINT) * 8
) / 1024 AS VARCHAR(30))+' MB')) AS [MaximumSize]
FROM
sys.master_files AS mf
INNER JOIN sys.databases AS db ON
db.database_id = mf.database_id
Executing following sql (It will only work when you don't have multiple mdf/ldf files for same database)
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
will return this output
DBName DataFile LogFile
--------------------------------------------------------------------------------
master C:\....\master.mdf C:\....\mastlog.ldf
tempdb C:\....\tempdb.mdf C:\....\templog.ldf
model C:\....\model.mdf C:\....\modellog.ldf
and rest of the databases
If your TempDB's have multiple MDF's (like mine have), this script will fail. However, you can use
WHERE db.database_id > 4
at the end and it will return all databases except system databases.
Using this script you can show all the databases name and files used (with exception of system dbs).
select name,physical_name from sys.master_files where database_id > 4
This script lists most of what you are looking for and can hopefully be modified to you needs. Note that it is creating a permanent table in there - you might want to change it. It is a subset from a larger script that also summarises backup and job information on various servers.
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]
Below script can be used to get following information: 1. DB Size Info 2. FileSpaceInfo 3. AutoGrowth 4. Recovery Model 5. Log_reuse_backup information
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
If you rename your Database, MS SQL Server does not rename the underlying files.
Following query gives you the current name of the database and the Logical file name (which might be the original name of the Database when it was created) and also corresponding physical file names.
Note: Un-comment the last line to see only the actual data files
select db.database_id,
db.name "Database Name",
files.name "Logical File Name",
files.physical_name
from sys.master_files files
join sys.databases db on db.database_id = files.database_id
-- and files.type_desc = 'ROWS'
Reference:
The query will error if multiple data files (e.g. ".ndf" file types) are used in one of the databases.
Here's a version of your query using joins instead of the sub-queries.
Cheers!
SELECT
db.name AS DBName,
db.database_id,
mfr.physical_name AS DataFile,
mfl.physical_name AS LogFile
FROM sys.databases db
JOIN sys.master_files mfr ON db.database_id=mfr.database_id AND mfr.type_desc='ROWS'
JOIN sys.master_files mfl ON db.database_id=mfl.database_id AND mfl.type_desc='LOG'
ORDER BY db.database_id
just adding my 2 cents .
if specifically looking to find total free space only in Data files or only in Log files in all the databases, we can use "data_space_id" column. 1 is for data files and 0 for log files.
CODE:
Create Table ##temp
(
DatabaseName sysname,
Name sysname,
spacetype sysname,
physical_name nvarchar(500),
size decimal (18,2),
FreeSpace decimal (18,2)
)
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name,spacetype, physical_name, Size, FreeSpace)
Select DB_NAME() AS [DatabaseName], Name, ***data_space_id*** , physical_name,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2))/1024 as nvarchar) SizeGB,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2)/1024 as decimal(18,2)) -
Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2))/1024 as nvarchar) As FreeSpaceGB
From sys.database_files'
select
databasename
, sum(##temp.FreeSpace)
from
##temp
where
##temp.spacetype = 1
group by
DatabaseName
drop table ##temp
You can use the below:
SP_HELPDB [Master]
GO
