none
Log_reuse_wait_desc is stacked to XTP_CHECKPOINT RRS feed

  • Question

  • I have a database on sql 2016 where usage of in memory tables took place. The Log_reuse_wait_desc is stacked to XTP_CHECKPOINT and this cause various issues:

    1. Restore operations cannot be complete (WAIT_XTP_TASK_SHUTDOWN wait) and the only way to create a new database from a backup is to bring offline the sql server /copy files and use attach instead of restore

    2. The transaction log is not truncated and is increased over the time (my data file is 919128 and the transaction log is 2519608 and used 99%).

    3. Database management operations like offline cannot be completed due to WAIT_XTP_TASK_SHUTDOWN wait.

    Database is on simple recovery model and the version of the sql is SP2-CU12) (KB4536648).

    I have try to take a full backup and a transaction log backup but with no success regarding the transaction log truncation. 

    Friday, May 29, 2020 6:32 AM

All replies

  • Hi xmaroulakou,

    1.Upgrade to the latest CU to test;

    2.Or try code as this:

    USE [master] 
    GO  
    ALTER DATABASE <db name> SET RECOVERY full  
    GO
    ALTER DATABASE <db name> SET RECOVERY SIMPLE WITH NO_WAIT;
    GO
    USE [db name]
    GO
    DBCC SHRINKFILE('<log file name>', 0, TRUNCATEONLY)

    This may be helpful: how-to-change-log-reuse-wait-and-log-reuse-wait-desc ,the-transaction-log-for-database-database-name-is-full-due-to-xtp-checkpointfix-the-log-wait-desc-column-shows-xtp-checkpoint-in-sys-databases-cat

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 2, 2020 1:35 AM
  • I have already try/check all those but with no success. I will install the latest CU and re-check but I don't believe that the latest CU resolves the issue since does not contain any related issue in the release notes.

    Thank you,

    Xenia

    Tuesday, June 2, 2020 2:56 PM
  • Hi xmaroulakou,

    Do you try the latest one?

    Is the issue still there?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 3, 2020 1:23 AM
  • Hi xmaroulakou,

    Is the issue solved?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, June 4, 2020 1:09 AM
  • Hi Yuxi,

    The issue has not be resolved.

    Xenia

    Tuesday, June 9, 2020 5:48 AM
  • Hi xmaroulakou,

    If the issue always exists after you have tested, please don't hesitate to ask professional engineers for help, and they will deal with your problem separately and confidentially: assistedsupportproducts

    Hope this could be helpful!

    Looking forward to your response.

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 10, 2020 9:28 AM