[MSSQL] 13. VLF 갯수 보는 쿼리

DB별 로그 사이즈, LDF 내 Log 사용률 등을 조회할 때 사용합니다.

DBCC SQLPERF(LOGSPACE)
GO

 

만약 DB별 Recovery Mode, VLF 갯수 등도 조회가 필요하다면 아래 쿼리도 사용 가능합니다.

 

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

CREATE TABLE #LOGFILECNT (
	 database_id INT
	,dbname SYSNAME
	,vlfcount INT
);
INSERT INTO #LOGFILECNT (database_id, dbname, vlfcount)
SELECT database_id, name, 0 AS vlfcount
FROM sys.databases
ORDER BY database_id

exec sp_MSforeachdb 'USE [?] CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30), c8 nvarchar(30));
					 INSERT INTO #T1 EXEC (''DBCC LOGINFO WITH NO_INFOMSGS'');
					 UPDATE #LOGFILECNT SET vlfcount = (SELECT count(*) FROM #T1) WHERE database_id = DB_ID();
					 DROP TABLE #T1;'

SELECT 
	 db.database_id AS [DBID]
	,db.[name] AS [DBName]
	,SUSER_SNAME(db.owner_sid) AS [Owner]
	,db.recovery_model_desc AS [RecModel]
	,db.log_reuse_wait_desc AS [LogReuse]
	,db.[compatibility_level] AS [CompLevel]
	,db.page_verify_option_desc AS [PageVerify]
	,db.snapshot_isolation_state_desc AS [Snapshot]
	,db.is_read_committed_snapshot_on AS [RCSI]
	,db.target_recovery_time_in_seconds AS [Indirect_TIme]
	,CASE 
		WHEN db.replica_id IS NULL THEN ''
		ELSE 'Y'
	 END AS isAG
	,ls.cntr_value/1024 AS [Log_Size_MB]
	,lu.cntr_value/1024 AS [Log_Used_MB]
	,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log_Used_%]
	,lf.vlfcount AS [VLF_Count]
FROM sys.databases AS db
INNER JOIN #LOGFILECNT AS lf
	ON db.database_id = lf.database_id
LEFT OUTER JOIN sys.dm_os_performance_counters AS lu ON db.NAME = lu.instance_name
LEFT OUTER JOIN sys.dm_os_performance_counters AS ls ON db.NAME = ls.instance_name
WHERE lu.counter_name LIKE 'Log FILE(s) Used Size (KB) % '
AND ls.counter_name LIKE 'Log FILE(s) Size (KB) % '
AND ls.cntr_value > 0
ORDER BY db.database_id
GO