Database File Size 정보 한번에 조회하기

SQL Server의 데이터베이스에는 데이터파일과 로그파일이 여러개로 구성 되어 있을 수 있습니다.
이 파일들의 크기를 파악하고 관리하는것은 DBA의 중요한 업무 중 하나입니다.
서버에 있는 모든 데이터베이스의 모든 파일에 관한 정보를 확인하려면 sys.master_files라는 뷰를 사용하면 되지만
안타깝게도 남은 용량에 관한 정보는 제공되지 않습니다. 좀 해주지...

다음 스크립트는 모든 파일에 대한 정보를 출력합니다.
IF OBJECT_ID('tempdb..#Database_Files', 'U') IS NOT NULL DROP TABLE #Database_Files
GO
CREATE TABLE #Database_Files (
database_id int, file_id int, file_guid uniqueidentifier, type tinyint, type_desc nvarchar(60), data_space_id int, name sysname, physical_name nvarchar(260), state tinyint, state_desc nvarchar(60)
, size int, used_size int, max_size nvarchar(16), growth nvarchar(16), is_media_read_only bit, is_read_only bit, is_sparse bit, is_percent_growth bit, is_name_reserved bit
, create_lsn numeric(25,0), drop_lsn numeric(25,0), read_only_lsn numeric(25,0), read_write_lsn numeric(25,0), differential_base_lsn numeric(25,0), differential_base_guid uniqueidentifier, differential_base_time datetime
, redo_start_lsn numeric(25,0), redo_start_fork_guid uniqueidentifier, redo_target_lsn numeric(25,0), redo_target_fork_guid uniqueidentifier, backup_lsn numeric(25,0))

DECLARE @SCRIPT NVARCHAR(MAX) = 'SET NOCOUNT ON' + CHAR(10)

SELECT @SCRIPT = @SCRIPT + CHAR(10) + 'USE ' + name + ';' + CHAR(10)
       + 'INSERT INTO #Database_Files (database_id, file_id, file_guid, type, type_desc, data_space_id, name, physical_name, state, state_desc, '
       + 'size, used_size, max_size, growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth, is_name_reserved, '
       + 'create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid, differential_base_time, '
       + 'redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn)' + CHAR(10)
       + 'SELECT database_id = ' + CONVERT(VARCHAR(16), database_id) + ', file_id, file_guid, type, type_desc, data_space_id, name, physical_name, state, state_desc, '
       + 'size, used_size = FILEPROPERTY(name, ''SpaceUsed''), '
       + 'max_size = CASE WHEN max_size = -1 OR (max_size = 268435456 AND type = ''1'') THEN ''UNLIMITED'' ELSE REPLACE(CONVERT(VARCHAR(30), CONVERT(MONEY, (max_size*8.0/1024)), 112), ''.00'', SPACE(0)) END,'
       + 'growth = CASE WHEN is_percent_growth = 1 THEN CONVERT(VARCHAR(8), growth) + ''%'' ELSE CONVERT(VARCHAR(512), CONVERT(INT, growth * 8.0 / 1024)) + ''MB'' END, '
       + 'is_media_read_only, is_read_only, is_sparse, is_percent_growth, is_name_reserved, '
       + 'create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid, differential_base_time, '
       + 'redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn' + CHAR(10)
       + 'FROM ' + name + '.sys.database_files;' + CHAR(10)
FROM   SYS.DATABASES

EXEC (@SCRIPT)

SELECT [DBID] = A.database_id
       , [DatabaseName] = A.name
       , [State] = A.state_desc
       , [AccessMode] = A.user_access_desc
       , [RecoveryModel] = A.recovery_model_desc
       , [Type] = B.type_desc
       , [LogicalName] = B.name
       , [PhysicalName] = B.physical_name
       , [Size] = CONVERT(NUMERIC(10, 2), B.size * 8.0 / 1024)
       , [UsedSize] = CONVERT(NUMERIC(10, 2), B.used_size * 8.0 / 1024)
       , [FreeSize] = CONVERT(NUMERIC(10, 2), (B.size - B.used_size) * 8.0 / 1024)
       , [FreeSize(%)] = CONVERT(NUMERIC(10, 2), (B.size - B.used_size) * 100.0 / B.size)
       , [max_size] = B.max_size
       , [Growth] = B.growth
FROM   SYS.DATABASES A
       INNER JOIN #Database_Files B ON A.database_id = B.database_id
ORDER BY CASE WHEN A.database_id < 5 THEN 1 ELSE 2 END, A.name