[MSSQL] 14. 데이터 파일 사용률 조회

DB별 파일 그룹, 물리적 파일 위치(mdf, ndf, ldf), 파일 크기, 파일 안에서의 실제 사용률, 빈공간 등을 조회할 때 사용할 수 있습니다.

 

IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
WHERE name not in ('master', 'msdb', 'model')
GO

DECLARE @dbname sysname
DECLARE @min_dbid smallint
DECLARE @SQL varchar(8000)

IF OBJECT_ID(N'tempdb..#DBSIZE') IS NOT NULL 
BEGIN
     DROP TABLE #DBSIZE
END

CREATE TABLE #DBSIZE (
	  database_id INT 
	, dbname nvarchar(500)
	, fileid INT
	, filename nvarchar(500)
	, type nvarchar(500)
	, fg_name nvarchar(500)
	, size_mb DECIMAL(10,2)
	, used_mb DECIMAL(10,2)
	, free_mb DECIMAL(10,2)
	, free_pecent DECIMAL(10,2)
	, growth nvarchar(500)
	, physical_name nvarchar(500)
);

SET @min_dbid = 0

WHILE (@min_dbid IS NOT NULL)
BEGIN
  SELECT @min_dbid = MIN(dbid) FROM #DB WHERE dbid > @min_dbid
  IF (@min_dbid IS NULL)  BREAK

  SELECT @dbname = name FROM #DB WHERE dbid = @min_dbid

  SET @SQL = 'USE [' + @dbname + ']; 
				SELECT 
				     DB_ID() AS database_id
					,DB_NAME() AS dbname
					,[fileid] = A.file_id
					,[FILE_Name] = A.name
					,[TYPE] = A.TYPE_DESC
					,[FILEGROUP_NAME] = fg.name
					,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
					,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - 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)
					,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128.0)/(A.SIZE/128.0))*100)
					,[AutoGrow] = ''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, BAD setting!]'' ELSE '''' END
					,[File_Location] = A.PHYSICAL_NAME
				FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
				ORDER BY A.file_id
			'

  INSERT INTO #DBSIZE (database_id, dbname, fileid, filename, type, fg_name, size_mb, used_mb, free_mb, free_pecent, growth, physical_name)
  EXEC (@SQL)

  UPDATE #DBSIZE SET dbname = @dbname WHERE dbname IS NULL
END

SELECT database_id AS DBID, dbname, fileid, filename, type, fg_name, size_mb, used_mb, free_mb, free_pecent, growth, physical_name  
FROM #DBSIZE 
ORDER BY database_id, fileid
GO