Sql-server – Will transaction log grow event trigger time out event

To fix the issue in the short term manually grow the log file since that's best practice anyway.

If the log file will end up being multiple GB grow it in 1 - 4 GB increments to get there.

If you don't know how big it's going to get I would recommend using at least 1 GB increments. This is a blocking operation so it should be done during a low use time if at all possible.

To discover why your problem is happening check for errors in the SQL error log, not just error message from the failed statement. There may be an error stating why it couldn't grow.

You can also look at activity monitor to see what the sessions that are having problems are waiting on. Right click on the instance in SQL Server Management Studio (SSMS) and click on "Activity Monitor." Once you have it open look at the blocking chain (Blocked By and Head Blocker) and the Wait Type to see what is causing the issue.

To determine why the DB is getting created with a MAXSIZE of 70 run Profiler to catch the CREATE DATABASE statement. That will tell you what query is getting run so you know if SQL is getting the correct query or not.

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'.