locked
I'm getting continues error like database log backup full. RRS feed

  • Question

  • Always getting error as below.

    The transaction log for database 'dbname' is full due to 'LOG_BACKUP' 

    I have less explore on SQL side, please help how to get fix as permanent  

    Saturday, August 8, 2020 1:47 PM

Answers

  • 1. Initially connect the source server and check the log file usage
    DBCC SQLPERF(LOGSPACE)

    2. If you see the Search_Service_DB database is 99% filled then check the drive where the db log files hosted filled up ?
    SP_HELPDB Search_Service_DB

    3. If your drive not filled, you can set auto growth to next level if its restricted. else you have to shrink in order to free up some space 

    4. Check why the log file is full, if its shows log_backup  as log_reuse_wait_desc then you have to take log backup to free up space.
    select name,log_reuse_wait_desc from sys.databases .

    5. Take a log backup 
    BACKUP LOG Search_Service_DB to disk ='location\Search_Service_DB.trn'

    6. Shrink the log file

    If the log_reuse_wait_desc is showing other than Log_backup,then you have to check further what else causing log file to get full. 
    Eg : Availabilty Replica, Replication, Active_trancation  for each one the fixes will be different. Hence when you get an error says log file is full, check what is causing the log file to get full then decide the fix.

    Most common reason, a database is running without log backups . So as a permanent fix you ca configured the databases for log backup if the recovery model is FULL/Bulk Logged. Search_Service_DB which can run in certain intervals depends the transactions happens on your source database.

    Let me know if you need any assistance in configuring job to take backups .

    Some log_reuse_wait_desc are,

    LOG_BACKUP - if this shows, the system allow you to shrink the log file when you take log backup. This will clear the committed transactions from the Log file which will release the space logically and then you can shrink if the logical space has to released to disk.

    ACTIVE_TRANSACTION - This will appear when you have an orphan transactions run on the DB (DBCC OPENTRAN() - this will help you get the active SPID). You have check the respective process and take appropriate action. Until the process completes/fails you cannot shrink the log file.

    REPLICATION - This is when your database is configured for replication and your database has some new transactions which is waiting to replicates to subscribers.

    AVAILABILITY_REPLICA - This will generally get when an always on delays in SYNC or not sync.

    So check what below query result and plan your next step. Since the shrink will only help when you see log_reuse_wait_desc as NOTHING. :)

    Hope this helps..
    if yes please vote.

    Thanks,
    Sreekanth Madambath
    • Marked as answer by Anugrahshah Saturday, August 8, 2020 2:23 PM
    Saturday, August 8, 2020 2:06 PM

All replies

  • 1. Initially connect the source server and check the log file usage
    DBCC SQLPERF(LOGSPACE)

    2. If you see the Search_Service_DB database is 99% filled then check the drive where the db log files hosted filled up ?
    SP_HELPDB Search_Service_DB

    3. If your drive not filled, you can set auto growth to next level if its restricted. else you have to shrink in order to free up some space 

    4. Check why the log file is full, if its shows log_backup  as log_reuse_wait_desc then you have to take log backup to free up space.
    select name,log_reuse_wait_desc from sys.databases .

    5. Take a log backup 
    BACKUP LOG Search_Service_DB to disk ='location\Search_Service_DB.trn'

    6. Shrink the log file

    If the log_reuse_wait_desc is showing other than Log_backup,then you have to check further what else causing log file to get full. 
    Eg : Availabilty Replica, Replication, Active_trancation  for each one the fixes will be different. Hence when you get an error says log file is full, check what is causing the log file to get full then decide the fix.

    Most common reason, a database is running without log backups . So as a permanent fix you ca configured the databases for log backup if the recovery model is FULL/Bulk Logged. Search_Service_DB which can run in certain intervals depends the transactions happens on your source database.

    Let me know if you need any assistance in configuring job to take backups .

    Some log_reuse_wait_desc are,

    LOG_BACKUP - if this shows, the system allow you to shrink the log file when you take log backup. This will clear the committed transactions from the Log file which will release the space logically and then you can shrink if the logical space has to released to disk.

    ACTIVE_TRANSACTION - This will appear when you have an orphan transactions run on the DB (DBCC OPENTRAN() - this will help you get the active SPID). You have check the respective process and take appropriate action. Until the process completes/fails you cannot shrink the log file.

    REPLICATION - This is when your database is configured for replication and your database has some new transactions which is waiting to replicates to subscribers.

    AVAILABILITY_REPLICA - This will generally get when an always on delays in SYNC or not sync.

    So check what below query result and plan your next step. Since the shrink will only help when you see log_reuse_wait_desc as NOTHING. :)

    Hope this helps..
    if yes please vote.

    Thanks,
    Sreekanth Madambath
    • Marked as answer by Anugrahshah Saturday, August 8, 2020 2:23 PM
    Saturday, August 8, 2020 2:06 PM
  • Thanks for the detailed info.
    Saturday, August 8, 2020 2:23 PM
  • Always getting error as below.

    The transaction log for database 'dbname' is full due to 'LOG_BACKUP' 

    I have less explore on SQL side, please help how to get fix as permanent  

    First you need to decide what level of disaster recovery you want. If this database goes belly up, are you satisfied with restoring the most recent backup? Or you do want to ensure that you lose as little data as possible?

    If you are content with restoring the most recent backup, do this:

    ALTER DATABASE CURRENT SET RECOVERY SIMPLE

    The log will now automatically be truncated regularly. You can use DBCC SHRINKFILE to shrink the log file if you wish.

    If you want up-to-the-point recovery, you need to schedule log backups. You will also need to learn to apply them, so that you know what to do in case of a disaster.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 8, 2020 9:59 PM