none
exponential grow of Transaction Log RRS feed

  • Question

  • I have a situation where Trn-log grown over 200GB (normal use is 20-40GB), digging into it seems the internal Checkpoint is not working. Manual run of Checkpoint, gets blocked by SLEEP_BPOOL_FLUSH wait and stopping the run shows below error:
    One or more recovery units belonging to database 'DWxxxx' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

    Any idea on how we can clear trn log , complete checkpoint - without rebooting the server?

    Thanks



    Mahesh

    Wednesday, February 14, 2018 5:46 PM

All replies

  • Followup info

    Found this error in event viewer:

    SQL Server Assertion: File: <PageFlush.cpp>, line=97 Failed Assertion = 'doneLSNs == block->m_pendingPages'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

    Running CheckDB ,  show lock wait: (2332ms)LCK_M_X, so when stopped throws below error msg:

    Msg 1823, Level 16, State 8, Line 1
    A database snapshot cannot be created because it failed to start.
    Msg 7928, Level 16, State 1, Line 1
    The database snapshot for online checks could not be created. 
    Either the reason is given in a previous error or one of the underlying volumes does not support sparse files
     or alternate streams. Attempting to get exclusive access to run checks offline.
    Msg 8921, Level 16, State 3, Line 1
    Check terminated. A failure was detected while collecting facts. 
    Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
    Query was canceled by user.


    Mahesh

    Wednesday, February 14, 2018 9:22 PM
  • So, are you out of disk space?  Do you have some open transactions holding a ton of tempdb space or otherwise blocking operations?

    Josh


    ps - and what version of SQL Server are you on?
    • Edited by JRStern Thursday, February 15, 2018 12:03 AM
    Thursday, February 15, 2018 12:03 AM
  • The problem seems tricky one the SLEEP_BPOOL_FLUSH wait comes into picture when checkpoint is waiting and it is waiting because it seems it is overwhelming the I/O of the disk subsystem. At the same time it seems you have corruption in database.

    I would suggest to run dbcc checkdb first and wait for it to complete. Run checkdb when load is relatively less on the system or during downtime.

    What is output of select @@version

    Can you upload SQl Server errorlog on shared drive and post the link here so we can access it


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, February 15, 2018 7:39 AM
    Moderator
  • SQL 2014 Enterprise. No blocking or big transaction running. It just seem to stuck and unable to close/clear the trn log. We had to restart the windows OS server as a last resort before it blew up disk space as even the SQL shutdown didn't work. The database went into recovery mode upon restart and took 2 hours 1TB Db to complete. The system is working normal now.

    Thanks


    Mahesh

    Friday, February 16, 2018 1:09 PM
  • SQL 2014 Enterprise. No blocking or big transaction running. It just seem to stuck and unable to close/clear the trn log. We had to restart the windows OS server as a last resort before it blew up disk space as even the SQL shutdown didn't work. The database went into recovery mode upon restart and took 2 hours 1TB Db to complete. The system is working normal now.

    Thanks


    Mahesh

    Please run checkdb on database and also ask storage team to do sanity check on storage

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Hannah Yu Monday, February 26, 2018 9:50 AM
    Saturday, February 17, 2018 5:06 AM
    Moderator