November 23, 2020 at 12:24 am
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
November 23, 2020 at 12:51 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 23, 2020 at 1:07 am
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?
November 23, 2020 at 5:40 am
This was removed by the editor as SPAM
November 23, 2020 at 5:46 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 23, 2020 at 10:26 pm
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....
November 23, 2020 at 10:41 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 1, 2020 at 9:17 pm
have you tried this,
SELECT name, log_reuse_wait_desc
FROM sys.DATABASES
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply