DB 파일 사용상태 쿼리

이건 DataFile 들

create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))

insert #tmpspc EXEC ('dbcc showfilestats')

SELECT

db_name() as DBName,

CAST(cast(g.name as varbinary(256)) AS sysname) AS [FileGroup_Name],

s.name AS [Name],

left(s.physical_name, 1) as DriveName,

s.physical_name AS [FileName],

(tspc.TotalExtents - tspc.UsedExtents)*convert(float,64) AS [AvailableSpace],

CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],

CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],

s.file_id AS [ID],

CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],

CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],

s.size * CONVERT(float,8) AS [Size],

CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],

CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],

s.is_read_only AS [IsReadOnly],

s.is_media_read_only AS [IsReadOnlyMedia],

s.is_sparse AS [IsSparse]

FROM

sys.filegroups AS g

INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id

ORDER BY

[FileGroup_Name] ASC,[Name] ASC

drop table #tmpspc

이건 Log 파일들

SELECT db_name() as DBName, null as [FileGroup_Name], s.name AS [Name], left(s.physical_name, 1) as DriveName, s.physical_name AS [FileName], s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) as [AvailableSpace], CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth], CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType], s.file_id AS [ID], null as IsPrimaryFile, CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize], s.size * CONVERT(float,8) AS [Size], CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace], CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline], s.is_read_only AS [IsReadOnly], s.is_media_read_only AS [IsReadOnlyMedia], s.is_sparse AS [IsSparse] FROM sys.master_files AS s WHERE (s.type = 1 and s.database_id = db_id()) ORDER BY [Name] ASC

이렇게 구경하면 됩니다. 음...