When I see this error it's usually because there was a long running transaction that has not been committed yet. Note that the size of the long running transaction isn't necessarily the problem. If the transaction changes any amount of data it will prevent the transaction log from clearing past that VLF. So if your transaction log is 1 GB and you do activity in the database that requires more than 1 GB of transaction log since your oldest active transaction you can run into an issue.
For a simple demo I have a database with a recovery model of
SIMPLE and a log limited to 100 MB. In one session I insert and delete data in a loop:
create table dbo.X_DUMMY_TABLE (COL VARCHAR(4000));
SET NOCOUNT ON;
-- truncate and insert data in a loop
WHILE 1 = 1
TRUNCATE TABLE X_DUMMY_TABLE
INSERT INTO dbo.X_DUMMY_TABLE
SELECT REPLICATE('Z', 4000)
With a recovery model of
SIMPLE this doesn't cause any issues. Using
DBCC SQLPERF(LOGSPACE) I can see that the log is between 1 - 7% full. Note that the transaction log file itself stays at 100 MB.
Now I'll start a transaction in another session that inserts just a single row. I won't commit the transaction:
create table dbo.X_DUMMY_TABLE_2 (COL VARCHAR(4000))
INSERT INTO dbo.X_DUMMY_TABLE_2
-- note the lack of a COMMIT
After about 3 seconds the first session throws this error:
Msg 9002, Level 17, State 4, Line 11
The transaction log for database 'SE_DB3' is full due to 'ACTIVE_TRANSACTION'.