none
How to fix this 'The transaction log for database 'Search_Service_DB' is full due to 'LOG_BACKUP'.? RRS feed

  • Question

  • Hi All,

    All of sudden my ssrs report stoped working and I found this in Event Viewer:

    "The Transaction log for database 'Search_Service_DB' is full due to 'Log_Backup'" How to fix this? thanks.

    Thursday, July 5, 2018 6:21 AM

Answers

All replies

  • I think you should try the below option:

    On your SQL Server, open the SQL Server Management Studio.  Connect to the local SQL Server.  Right click Search_Service_DB -> Properties -> Options.  Change Recover Model from Full to Simple.  Click OK.  Right click Search_Service_DB, go to Tasks -> Shrink -> Shrink Files.  Select a File type of Log, then Shrink.

    OR below SQL queries:

    USE {database-name};  
    GO  
    -- Truncate the log by changing the database recovery model to SIMPLE.  
    ALTER DATABASE {database-name}
    SET RECOVERY SIMPLE;  
    GO  
    -- Shrink the truncated log file to 1 MB.  
    DBCC SHRINKFILE ({database-file-name}, 1);  
    GO  
    -- Reset the database recovery model.  
    ALTER DATABASE {database-name}
    SET RECOVERY FULL;  
    GO

    Thursday, July 5, 2018 6:35 AM
  • Hi Sam,

    SQL Server Database Engine issues this 9002 error when the transaction log becomes full. Add M J's post, to avoid it in the future, we can use the methods below:

    • Backing up the log.
    • Freeing disk space so that the log can automatically grow.
    • Moving the log file to a disk drive with sufficient space.
    • Increasing the size of a log file.
    • Adding a log file on a different disk.
    • Completing or killing a long-running transaction.

    More infomarion:

    https://docs.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    Friday, July 6, 2018 8:19 AM
    Moderator
  • Hi M J,

    Is there any update?

    If the post helps you, you can mark it as answer.

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, July 13, 2018 9:18 AM
    Moderator