The transaction log for the database is full

To fix this problem, change Recovery Model to Simple then Shrink Files Log

1. Database Properties > Options > Recovery Model > Simple

2. Database Tasks > Shrink > Files > Log

Done.

Then check your db log file size at Database Properties > Files > Database Files > Path

To check full sql server log: open Log File Viewer at SSMS > Database > Management > SQL Server Logs > Current

I had this error once and it ended up being the server's hard drive that run out of disk space.

Is this a one time script, or regularly occurring job?

In the past, for special projects that temporarily require lots of space for the log file, I created a second log file and made it huge. Once the project is complete we then removed the extra log file.

Do you have Enable Autogrowth and Unrestricted File Growth both enabled for the log file? You can edit these via SSMS in "Database Properties > Files"

This is an old school approach, but if you're performing an iterative update or insert operation in SQL, something that runs for a long time, it's a good idea to periodically (programmatically) call "checkpoint". Calling "checkpoint" causes SQL to write to disk all of those memory-only changes (dirty pages, they're called) and items stored in the transaction log. This has the effect of cleaning out your transaction log periodically, thus preventing problems like the one described.

The following will truncate the log.

USE [yourdbname] 
GO

-- TRUNCATE TRANSACTION LOG --
DBCC SHRINKFILE(yourdbname_log, 1)
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(yourdbname_log, 1)
GO

-- CHECK DATABASE HEALTH --
ALTER FUNCTION [dbo].[checker]() RETURNS int AS BEGIN  RETURN 0 END
GO

If your database recovery model is full and you didn't have a log backup maintenance plan, you will get this error because the transaction log becomes full due to LOG_BACKUP.

This will prevent any action on this database (e.g. shrink), and the SQL Server Database Engine will raise a 9002 error.

To overcome this behavior I advise you to check this The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP that shows detailed steps to solve the issue.

I met the error: "The transaction log for database '...' is full due to 'ACTIVE_TRANSACTION' while deleting old rows from tables of my database for freeing disk space. I realized that this error would occur if the number of rows to be deleted was bigger than 1000000 in my case. So instead of using 1 DELETE statement, i divided the delete task by using DELETE TOP (1000000).... statement.

For example:

instead of using this statement:

DELETE FROM Vt30 WHERE Rt < DATEADD(YEAR, -1, GETDATE())

using following statement repeatedly:

DELETE TOP(1000000) FROM Vt30 WHERE Rt < DATEADD(YEAR, -1, GETDATE())

Try this:

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

I hope it helps.

adding up to the answers above, I also want to mention that, if possible, u can also free up the server to fix this issue. If the server is already full due to the database overflow, u can delete some unnecessary files from the SERVER where ur DB is built upon. At least this temporarily fixes the issue and lets you to query the DB

My problem solved with multiple execute of limited deletes like

Before

DELETE FROM TableName WHERE Condition

After

DELETE TOP(1000) FROM TableName WHERECondition

The answer to the question is not deleting the rows from a table but it is the the tempDB space that is being taken up due to an active transaction. this happens mostly when there is a merge (upsert) is being run where we try to insert update and delete the transactions. The only option is is to make sure the DB is set to simple recovery model and also increase the file to the maximum space (Add an other file group). Although this has its own advantages and disadvantages these are the only options.

The other option that you have is to split the merge(upsert) into two operations. one that does the insert and the other that does the update and delete.

Here's my hero code. I've faced this problem. And use this code to fix this.

 USE master;

    SELECT 
        name, log_reuse_wait, log_reuse_wait_desc, is_cdc_enabled 
    FROM 
        sys.databases 
    WHERE 
        name = 'XX_System';

    SELECT DATABASEPROPERTYEX('XX_System', 'IsPublished');


    USE XX_System;
    EXEC sp_repldone null, null, 0,0,1;
    EXEC sp_removedbreplication XX_System;


    DBCC OPENTRAN;
    DBCC SQLPERF(LOGSPACE);
    EXEC sp_replcounters;



    DBCC SQLPERF(LOGSPACE);

Try this:

If possible restart the services MSSQLSERVER and SQLSERVERAGENT.