"The transaction log for database is full due to 'LOG_BACKUP'" in a shared host

Alireza Noori picture Alireza Noori · Jan 20, 2014 · Viewed 244.3k times · Source

I have an Asp.Net MVC 5 website with EntityFramework codefirst approach in a shared hosting plan. It uses the open source WebbsitePanel for control panel and its SQL Server panel is somewhat limited. Today when I wanted to edit the database, I encountered this error:

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

I searched around and found a lot of related answers like this and this or this but the problem is they suggest running a query on the database. I tried running

db.Database.ExecuteSqlCommand("ALTER DATABASE db_name SET RECOVERY SIMPLE;");

with the visual studio (on the HomeController) but I get the following error:

System.Data.SqlClient.SqlException: ALTER DATABASE statement not allowed within multi-statement transaction.

How can I solve my problem? Should I contact the support team (which is a little poor for my host) or can I solve this myself?

Answer

Mohit Dharmadhikari picture Mohit Dharmadhikari · Jun 6, 2017

In Addition to Ben's Answer, You can try Below Queries as per your need

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 

Update Credit @cema-sp

To find database file names use below query

select * from sys.database_files;