Simple Recovery Model - Active VLFs cannot be truncated

Question

Post reply

Simple Recovery Model - Active VLFs cannot be truncated

jdlayton83

Old Hand

Points: 334
More actions
November 23, 2020 at 12:24 am

#3812819
1. 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?
Some details:
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:
LOP_INSERT_ROWS LCX_INDEX_LEAF
It seems this is still holding transactions made on an index that probably failed back then.
Any clues?
Thanks
- This topic was modified 3 years, 5 months ago by jdlayton83. Reason: subject mistake
- This topic was modified 3 years, 5 months ago by jdlayton83.
November 23, 2020 at 5:40 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100949 More actions · Answer 1

There must be some active transaction (or replication or other task that needs the log records).

What does DBCC OPENTRAN on that db show?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jdlayton83 Old Hand Points: 334 More actions · Answer 2

Thanks! no active transactions.

As far as I know replication is not running.

I did deploy in-memory tables once, huge mistake. I couldn't delete memory optimized filegroup, so I took it offline.

Could an offline filegroup be the cause?

This reply was modified 3 years, 5 months ago by jdlayton83.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

I would think that would be possible.

Can you bring that filegroup back online and REMOVE it? I think that would clear it up, if that was the issue.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jdlayton83 Old Hand Points: 334 More actions · Answer 4

As far as I know, after doing a lot of research, once you deploy in memory tables you cannot delete filegroups. It's some kind of product limitation....

This reply was modified 3 years, 5 months ago by jdlayton83.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 5

jdlayton83 wrote:

As far as I know, after doing a lot of research, once you deploy in memory tables you cannot delete filegroups. It's some kind of product limitation....

Yep, that is true. You should be able to remove all containers in that filegroup (if necessary, empty them all first).

That should at least let you clear the log file. Or at least I can't imagine that SQL Server would make it impossible to reduce a log size if you've ever created an in-memory table.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

tmisganaw Old Hand Points: 315 More actions · Answer 6

have you tried this,

SELECT name, log_reuse_wait_desc

FROM sys.DATABASES