Simple Recovery Model - Active VLFs cannot be truncated

    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 2 months ago by  jdlayton83. Reason: subject mistake
    • This topic was modified 2 months ago by  jdlayton83.
  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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 2 months ago by  jdlayton83.
  • This was removed by the editor as SPAM

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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 2 months ago by  jdlayton83.
  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • have you tried this,

    SELECT name, log_reuse_wait_desc

    FROM sys.DATABASES

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

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