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

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.

Answer 1

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

Answer 2

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 [e-mail].


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

Answer 3

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 [e-mail].


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

Answer 4

https://shayanarshi.blogspot.com/2020/01/error-9002-transaction-log-for-database.html


this blog will resolve the error commonly known as 9002

Answer 5

https://shayanarshi.blogspot.com/2020/01/error-9002-transaction-log-for-database.html


this blog will resolve the error commonly known as 9002

Answer 6

I recently ran into this issue and this article The transaction log for database SharePoint_Config is full due to LOG_BACKUP helped me to fix this issue by doing

• Take a Full database backup.
• Shrink the log file to reduce the physical file size.
• Create a LOG_BACKUP.
• Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

Answer 7

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_transaction  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