Sql-server – SQL Server transaction log file refuses to grow

sql servertransaction-log

Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB.

Error received:

The transaction log for database 'Temp' is full. To find out why space
in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases

Questions:

  1. How do I fix this?
  2. Why is this happening?

Code:

CREATE DATABASE Temp ON PRIMARY(
       NAME = Temp
       , FILENAME = 'C:\\Temp.mdf'
       , SIZE = 2MB
       , FILEGROWTH = 10%) 
LOG ON (
       NAME = Temp_Log
       , FILENAME = 'C:\\Temp.ldf'
       , SIZE = 1MB, MAXSIZE = 70MB
       , FILEGROWTH = 10%)

Result of select name, log_reuse_wait_desc from sys.databases where name = 'temp'; :

name, log_reuse_wait_desc
Temp, NOTHING

Additional Information:

  1. Sql Server manager says maxsize = 5mb and restricted growth
  2. Recovery mode Simple
  3. Database is handling hundreds of individual SQL Statements as it is for a data integration tool

Updated Addition information:

When the SQL statement is run directly in the SQL server manager it creates successfully and does not cause errors on implementation…On the other hand if the create database statement is run progmatically using System.Data.SqlClient and the exact same syntax it creates the Database with all defaults. I have to additionally supply alter database statements to achieve the proper behavior.

Code to run Queries:

public void AcuConvert()
        {
            using (DestD)
            {
                SqlCommand command = new SqlCommand();
                DestD.Open();
                command.Connection = DestD;
                foreach (var item in Entity.SqlDestinationQueries.ToList())
                {
                    command.CommandText = item.Query;
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.SystemQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Sys", SysD.Database);
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.InsertQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Source", SourceD.Database); ;
                    command.ExecuteNonQuery();
                }

            }
        }

Best Answer

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.

Related Question