3

Why do I get The transaction log for database 'X' is full due to 'ACTIVE_TRANSACTION' on a database with Simple recovery model and auto-growing log file?

The failing operation is executing many, many Insert statements. The insert statements are not occurring inside a transaction. I don't understand why the transaction log has to be so large for this and I don't understand why it's unable to grow.

7

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
BEGIN
    TRUNCATE TABLE X_DUMMY_TABLE

    BEGIN TRANSACTION
    INSERT INTO dbo.X_DUMMY_TABLE
    SELECT REPLICATE('Z', 4000)
    FROM dbo.getNums(100);
    COMMIT TRANSACTION;

    CHECKPOINT;
END;

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

BEGIN TRANSACTION

INSERT INTO dbo.X_DUMMY_TABLE_2
SELECT 'Z';
-- 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'.

|improve this answer|||||
2

In my case, something changed in DB properties-->Files Autogroth for Log file was disabled. Enabling it fixed the problem.

db properties

|improve this answer|||||
  • Note that in your case a 20mb logfile is incremental by 10% increments, I.e. 2mb. That's a pretty small base size and small increments. – eckes Jan 29 at 11:34
  • it fixed for me too – Yosep Tito Mar 13 at 5:33
0

One reason I got this was due to post processing of data that we imported from flat files. We needed the import to be resilient to changes to the data structures provided to us in the CSV, and one of the requirements was to shrink the data to the smallest allowed data types possible, for backwards-compatibility. (We also had to remove all NULLs, that's another story.)

The way I had solved it was to analyze all *char fields for the maximum length of data, using the information schema, and then using dynamic SQL to ALTER TABLE <table> ALTER COLUMN <column> <new-datatype-spec> on each field, in a single SQL query. For huge sets of data, this would not only bomb this process, but any other subsequent processes.

Changing this process to rename the original table, then SELECT <transformed fields> into <new-table-name> FROM <old-table> fixed the issue entirely.

|improve this answer|||||
0

I had interesting scenario, was not able to change file growth setting to unlimited. I had to add additional log file to bring the database back to healthy state and them update log file setting!

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.