April 3, 2011 at 8:00 pm
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
Thanks
April 3, 2011 at 10:04 pm
Try this code...
USE [master]
GO
CREATE PROCEDURE [dbo].[dba_rpt_SQL_Files_Report]
AS
/*
By Norm Enger - 2007 - http://thesqlguy.wordpress.com/
Reports on the file info for all dbs on a SQL instance, SQL 2000 and above
EXEC dba_rpt_SQL_Files_Report
*/
SET NOCOUNT ON
DECLARE @cmd1 VARCHAR(8000)
DECLARE @cmd2 VARCHAR(8000)
DECLARE @cmd3 VARCHAR(8000)
CREATE TABLE #data(
dbname VARCHAR(300),
dbcompat VARCHAR(10),
Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL,
FilePath VARCHAR(500) NULL,
Autogrowth VARCHAR(500) NULL,
init_size_MB DECIMAL (38,7) NULL,
tempdb_growth_MB DECIMAL (38,7) NULL
)
CREATE TABLE #log(
id int identity(1,1) not null,
groupid INT DEFAULT 0 NULL,
dbname sysname NOT NULL,
dbcompat VARCHAR(10),
--LogSize numeric(15,7) NOT NULL,
LogSize numeric(38,7) NOT NULL,
--LogUsed numeric(9,5) NOT NULL,
LogUsed numeric(38,6) NOT NULL,
Status int NOT NULL,
[Name] VARCHAR(300) NULL,
FilePath VARCHAR(500) NULL,
Autogrowth VARCHAR(500) NULL,
init_size_MB DECIMAL (38,7) NULL,
tempdb_growth_MB DECIMAL (38,7) NULL
)
DECLARE @dbs table (name sysname, status varchar(100), dbcompat varchar(10))
DECLARE @CurrDB varchar(500)
DECLARE @CurrCmptlevel varchar(10)
INSERT INTO @dbs
(name, dbcompat)
SELECT name, cmptlevel
FROM sysdatabases
WHERE category IN ('0', '1','16')
AND CAST(DATABASEPROPERTYEX (name, 'status') AS VARCHAR(500)) = 'ONLINE'
ORDER BY name
SELECT @CurrDB = MIN(name) FROM @dbs
SELECT @CurrCmptlevel = dbcompat FROM @dbs WHERE name = @CurrDB
--Data files
WHILE @CurrDB IS NOT NULL
BEGIN
SET @cmd1 = 'USE [' + @CurrDB + ']; INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName) EXEC(''DBCC showfilestats with no_infomsgs''); UPDATE #data SET dbname = ''' + @CurrDB + ''', dbcompat = ''' + CAST(@CurrCmptlevel AS VARCHAR(10)) + ''' WHERE dbname IS NULL; update #data set [FilePath] = s.filename, Autogrowth = ''Autogrowth: '' + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN ''None'' WHEN s.status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + '' MB'' ELSE ''By '' + CONVERT(VARCHAR, s.growth) + '' percent'' END + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN '''' WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth'' ELSE '', restricted growth to '' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + '' MB'' END from #data d inner join dbo.sysfiles s on d.FileGroup = s.groupid and d.Fileid = s.fileid where d.dbname = ''' + @CurrDB + ''''
EXEC (@cmd1)
SELECT @CurrDB = MIN(name) FROM @dbs WHERE name > @CurrDB
SELECT @CurrCmptlevel = dbcompat FROM @dbs WHERE name = @CurrDB
END
SELECT @CurrDB = MIN(name) FROM @dbs
SELECT @CurrCmptlevel = dbcompat FROM @dbs WHERE name = @CurrDB
--Log files
WHILE @CurrDB IS NOT NULL
BEGIN
--Update log file logical names
SET @cmd3 = 'USE [' + @CurrDB + ']; INSERT #log (dbname, LogSize, LogUsed, Status) EXEC(''DBCC sqlperf(logspace) with no_infomsgs''); update #log set [Name] = s.name, [FilePath] = s.filename, Autogrowth = ''Autogrowth: '' + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN ''None'' WHEN s.status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + '' MB'' ELSE ''By '' + CONVERT(VARCHAR, s.growth) + '' percent'' END + CASE WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00) OR s.growth = 0 THEN '''' WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth'' ELSE '', restricted growth to '' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + '' MB'' END from #log l inner join dbo.sysfiles s on l.groupid = s.groupid where l.dbname = ''' + @CurrDB + '''; update #log set dbcompat = ''' + @CurrCmptlevel + ''' where dbname = ''' + @CurrDB + ''''
EXEC (@cmd3)
SELECT @CurrDB = MIN(name) FROM @dbs WHERE name > @CurrDB
SELECT @CurrCmptlevel = dbcompat FROM @dbs WHERE name = @CurrDB
END
--Clean up #log
DELETE #log where Name IS NULL
DELETE #log FROM #log l
lEFT JOIN
(
SELECT dbname, Name, MIN(id) AS id FROM #log GROUP BY dbname, Name
) k
ON l.dbname = k.dbname AND l.Name = k.Name AND l.id = k.id
WHERE k.id IS NULL
--Get tempdb initial file sizes - update tempdb data files init sizes
UPDATE [#data]
SET [init_size_MB] = a.Initial_Size_MB
FROM #data d
INNER JOIN
(
SELECT
[DB_Name] = 'tempdb',
f.[name] AS logical_file_Name,
(CAST(f. AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
FROM master..sysaltfiles f
INNER JOIN master..sysdatabases d
ON f.dbid = d.dbid
WHERE d.name = 'tempdb') AS a
ON d.dbname = a.[DB_Name]
AND d.[Name] = a.logical_file_Name
--Get tempdb initial file sizes - update tempdb log file init sizes
UPDATE [#log]
SET [init_size_MB] = a.Initial_Size_MB
FROM #log l
INNER JOIN
(
SELECT
[DB_Name] = 'tempdb',
f.[name] AS logical_file_Name,
(CAST(f. AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
FROM master..sysaltfiles f
INNER JOIN master..sysdatabases d
ON f.dbid = d.dbid
WHERE d.name = 'tempdb') AS a
ON l.dbname = a.[DB_Name]
AND l.[Name] = a.logical_file_Name
--Calculate tempdb files growth
UPDATE [#data]
SET [tempdb_growth_MB] =
CASE
WHEN ((TotalExtents*64)/1024.0) - init_size_MB < 0.00 THEN 0.00
ELSE ((TotalExtents*64)/1024.0) - init_size_MB
END
UPDATE [#log]
SET [tempdb_growth_MB] =
CASE
WHEN LogSize - init_size_MB < 0.00 THEN 0.00
ELSE LogSize - init_size_MB
END
--Return results for all dbs
SELECT
dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')' AS [DB_NAME],
dbname AS DB_NAME_2,
'Data File' AS [Type],
[Name] AS [NAME],
[FilePath],
init_size_MB,
(TotalExtents*64)/1024.0 AS [TotalMB],
tempdb_growth_MB,
(UsedExtents*64)/1024.0 AS [UsedMB],
(TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB],
[Autogrowth]
FROM #data
UNION
SELECT
dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')' AS [DB_NAME],
dbname AS DB_NAME_2,
'TLog File',
[Name] AS [NAME],
[FilePath],
init_size_MB,
LogSize,
tempdb_growth_MB,
((LogUsed/100)*LogSize),
LogSize - ((LogUsed/100)*LogSize),
[Autogrowth]
FROM #log
ORDER BY 1, [Type], [Name]
DROP TABLE #data
DROP TABLE #log
GO
April 3, 2011 at 11:28 pm
Sure, easy query.
SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2011 at 9:39 am
Thanks, the easy query works better.
April 19, 2013 at 5:11 am
How about SELECT *
FROM [master].[dbo].[sysaltfiles] for SQL Server 2000?
Easy!
February 13, 2015 at 12:53 am
SQLCMD -S . -d Operdb -E -Q “SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
FROM sys.master_files AS mf” -s “,” -o “C:\result.csv”
it come outside from command prompt.. how to use ?
March 15, 2016 at 9:55 am
I deleted my database via Management Studio and was attempting to re-create it with the same name and I get the "logical filename <...> is already in use". I looked at sys.master_files and didn't see any remnants, but there must be somewhere. How do I clean this up, so I can use the same name? Thanks.
March 15, 2016 at 10:02 am
nevermind
March 15, 2016 at 11:17 pm
lesemv (3/15/2016)
nevermind
What did you find to make you say "nevermind"?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 6:00 am
I'm embarrassed to say. I had the same 'Name' value for the data and log file.
March 16, 2016 at 7:26 am
lesemv (3/16/2016)
I'm embarrassed to say. I had the same 'Name' value for the data and log file.
Heh... thanks. I'm sure that none of us have ever done that before. 😉 Good to see that I'm not the only one. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply