- Hi everyone,
I am facing a strange situation. I've got 99% of VLFs active and ckeckpoint is not working. No open transactions/long-running transactions.
Is there any chance log is holding active VLFs by mistake?
Is there any rare situation sql server considers VLFs are active?
SELECT [name] AS 'Database Name',
COUNT(li.database_id) AS 'VLF Count',
SUM(li.vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF',
SUM(li.vlf_active*li.vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF',
SUM(li.vlf_size_mb)-SUM(li.vlf_active*li.vlf_size_mb) AS 'Inactive VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) li
GROUP BY [name]
ORDER BY COUNT(li.database_id) DESC;
Database Name VLF Count VLF Size (MB) Active VLF Active VLF Size (MB) Inactive VLF Inactive VLF Size (MB)
******** 1012 299999.76 984 207811.39 2 8 92188.37
select * from sys.databases
******* SIMPLE CHECKPOINT
I see a lot of records in fn_dblog like:
It seems this is still holding transactions made on an index that probably failed back then.
- This topic was modified 2 months ago by jdlayton83. Reason: subject mistake
- This topic was modified 2 months ago by jdlayton83.