SQL Server의 모든 데이터베이스 파일에 대한 정보 나열
SQL Server 상의 모든 데이터베이스의 파일(MDF/LDF)에 대한 정보를 나열할 수 있습니까?
어떤 데이터베이스가 로컬 디스크의 어떤 파일을 사용하고 있는지 보여 주는 목록을 받고 싶습니다.
내가 시도한 것:
exec sp_databases모든 데이터베이스select * from sys.databases에는 각 데이터베이스에 대한 많은 정보가 표시되지만 유감스럽게도 각 데이터베이스에서 사용되는 파일은 표시되지 않습니다.select * from sys.database_files에 의 mdf/ldf 파일을 나타냅니다.master데이터베이스 - 다른 데이터베이스 이외
sys.master_files를 사용할 수 있습니다.
마스터 데이터베이스에 저장된 데이터베이스의 파일당 행을 포함합니다.이것은 시스템 전체의 단일 뷰입니다.
데이터베이스 위치를 가져오려면 Get All DBs Location을 선택하면 됩니다.
사용할 수 있습니다.sys.master_filesdb 위치 가져오기 및sys.databaseDB 이름을 가져오다
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
스크립트를 사용하여 각 파일의 빈 공간을 확보합니다.
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
크기는 KB 단위로 표시됩니다.
다음 쿼리를 만들었습니다.
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
다음 sql 실행(같은 데이터베이스에 여러 mdf/ldf 파일이 없는 경우에만 작동합니다)
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
이 출력을 반환합니다.
DBName DataFile LogFile
--------------------------------------------------------------------------------
master C:\....\master.mdf C:\....\mastlog.ldf
tempdb C:\....\tempdb.mdf C:\....\templog.ldf
model C:\....\model.mdf C:\....\modellog.ldf
기타 데이터베이스
사용 중인 TempDB에 여러 MDF가 있는 경우(저와 마찬가지로) 이 스크립트는 실패합니다.단,
WHERE db.database_id > 4
시스템 데이터베이스를 제외한 모든 데이터베이스를 반환합니다.
이것도 시도해 보세요.
select db_name(dbid) dbname, filename from sys.sysaltfiles
아래 스크립트를 사용하여 1. DB 크기 정보 2.FileSpaceInfo 3 。AutoGrowth 4.리커버리 모델5Log_reuse_백업 정보
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
이 스크립트를 사용하면 system dbs를 제외한 모든 데이터베이스 이름과 파일을 표시할 수 있습니다.
select name,physical_name from sys.master_files where database_id > 4
여러 데이터 파일(예: ".ndf" 파일 형식)이 존재할 때 오류가 발생하는 쿼리를 피하기 위해 이 버전을 사용해 보십시오. 이 버전은 하위 쿼리를 join으로 대체합니다.
다음은 하위 쿼리 대신 조인을 사용한 쿼리 버전입니다.
건배!
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
샘플 결과: (단일 로그 파일은 단일 데이터베이스에 대해 각 MDF 및 NDF와 쌍으로 구성되어 있습니다.)
이 스크립트는 사용자가 찾고 있는 대부분의 항목을 나열하며 필요에 따라 수정할 수 있습니다.영구 테이블을 만들고 있습니다. 변경할 수도 있습니다.또한 다양한 서버의 백업 및 작업 정보를 요약하는 더 큰 스크립트의 하위 집합입니다.
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]
데이터베이스 이름을 바꾸면 MS SQL Server는 기본 파일의 이름을 바꾸지 않습니다.
다음 쿼리는 데이터베이스의 현재 이름과 논리 파일 이름(데이터베이스가 생성되었을 때 데이터베이스의 원래 이름일 수 있음) 및 대응하는 실제 파일 이름을 제공합니다.
주의: 마지막 줄의 코멘트를 해제하여 실제 데이터 파일만 표시합니다.
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'
레퍼런스:
sp_MSForEachDB 저장 프로시저를 사용하는 것도 옵션입니다.
EXEC sp_MSForEachDB 'use ? select * from sys.database_files'
전체 경로 이름과 크기 정보만 보려면
EXEC sp_MSForEachDB '
USE [?];
SELECT DB_NAME() AS DbName,
physical_name AS FullPath,
name AS FileName,
type_desc,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
'
내 2센트만 더하면 돼
특히 데이터 파일 또는 모든 데이터베이스의 로그 파일에서만 사용 가능한 총 공간을 찾는 경우 "data_space_id" 열을 사용할 수 있습니다. 1은 데이터 파일, 0은 로그 파일입니다.
코드:
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
또한 다음 SQL 쿼리를 사용하여 파일 목록을 가져올 수도 있습니다.
SELECT d.name AS DatabaseName,
m.name AS LogicalName,
m.physical_name AS PhysicalName,
size AS FileSize
FROM sys.master_files m
INNER JOIN sys.databases d ON(m.database_id = d.database_id)
where d.name = '<Database Name>'
ORDER BY physical_name ;
다음을 사용할 수 있습니다.
SP_HELPDB [Master]
GO
언급URL : https://stackoverflow.com/questions/9630279/listing-information-about-all-database-files-in-sql-server
'programing' 카테고리의 다른 글
| Mac OS 10.10+에서 GNU sed를 사용하는 방법, 'brew install --default-names'는 더 이상 지원되지 않습니다. (0) | 2023.04.19 |
|---|---|
| 배치 파일에서 매개 변수가 비어 있는지 테스트하는 적절한 방법은 무엇입니까? (0) | 2023.04.19 |
| Ruby에서 문자열을 소문자로 변환하는 방법 (0) | 2023.04.19 |
| 현재 체크아웃된 Git 브랜치를 프로그래밍 방식으로 결정하는 방법 (0) | 2023.04.19 |
| iOS 또는 MacOS에서 활성 인터넷 연결을 확인하는 방법은 무엇입니까? (0) | 2023.04.19 |