7

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();
                }

            }
        }

migrated from stackoverflow.com Oct 1 '12 at 14:10

This question came from our site for professional and enthusiast programmers.

3

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.

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.